Introduction to JOINs

When data is spread across multiple tables, you need a way to combine it. That is exactly what JOINs do. They let you query data from two or more tables in a single SELECT statement. This is the whole point of a relational database — keeping data in separate tables and linking them together when needed.

Why JOINs?

Imagine you have a students table and an enrollments table. Without JOINs, you would need to run separate queries and combine the results in your application code. That is slow and messy.

With JOINs, the database does the work for you. It matches rows from different tables based on a condition and returns them as a single result set.

CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  credits INTEGER DEFAULT 3
);

CREATE TABLE enrollments (
  id SERIAL PRIMARY KEY,
  student_id INTEGER REFERENCES students(id),
  course_id INTEGER REFERENCES courses(id),
  grade CHAR(2)
);

The JOIN Syntax

The basic pattern for a JOIN:

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column;

The ON clause defines how the tables are related. It specifies which columns to match between the two tables.

Types of JOINs

There are several types of JOINs, each serving a different purpose:

• INNER JOIN — Returns only rows that have matches in both tables. The most common type.
• LEFT JOIN — Returns all rows from the left table, and matching rows from the right table. If there is no match, NULL values are returned for right table columns.
• RIGHT JOIN — The opposite of LEFT JOIN. Returns all rows from the right table.
• FULL JOIN — Returns all rows from both tables. If there is no match, NULL values fill in the gaps.
• CROSS JOIN — Returns the Cartesian product — every combination of rows from both tables.

We will cover each of these in detail in the next few lessons.

A Simple Example

Let us see students alongside their enrolled courses:

SELECT
  students.first_name,
  students.last_name,
  courses.name AS course_name
FROM students
JOIN enrollments ON students.id = enrollments.student_id
JOIN courses ON enrollments.course_id = courses.id;

This query joins three tables together. It shows each student's name alongside the courses they are enrolled in. Notice how we use table aliases to make the query more readable.

Table Aliases

When writing JOINs, typing full table names gets tedious. Use aliases:

SELECT
  s.first_name,
  s.last_name,
  c.name AS course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

The alias comes after the table name. Now s means students, e means enrollments, and c means courses. Much cleaner.