SQL — Joins

Why joins exist

Real databases store data in multiple tables to avoid duplication. Joins combine rows from two or more tables based on a related column.

-- Students table
CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(100));
INSERT INTO students VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

-- Enrollments table
CREATE TABLE enrollments (student_id INT, course VARCHAR(100));
INSERT INTO enrollments VALUES (1, 'Math'), (1, 'Science'), (2, 'Math');

Alice is enrolled in two courses. Bob is in one. Charlie isn't enrolled in any.

INNER JOIN — matching rows only

SELECT s.name, e.course
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id;

Output:

+---------+---------+
| name    | course  |
+---------+---------+
| Alice   | Math    |
| Alice   | Science |
| Bob     | Math    |
+---------+---------+

Charlie is missing — he has no enrollment. INNER JOIN only returns rows where there's a match in both tables.

LEFT JOIN — all from left table

SELECT s.name, e.course
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id;

Output:

+---------+---------+
| name    | course  |
+---------+---------+
| Alice   | Math    |
| Alice   | Science |
| Bob     | Math    |
| Charlie | NULL    |
+---------+---------+

All students appear. Charlie has NULL for course because he has no enrollment. LEFT JOIN keeps every row from the left table.

RIGHT JOIN — all from right table

SELECT s.name, e.course
FROM students s
RIGHT JOIN enrollments e ON s.id = e.student_id;

RIGHT JOIN keeps every row from the right table. In practice, you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping table order.

FULL JOIN — all from both tables

SELECT s.name, e.course
FROM students s
FULL JOIN enrollments e ON s.id = e.student_id;

Returns all rows from both tables. Non-matching rows get NULL on the missing side.

CROSS JOIN — every combination

SELECT s.name, c.course_name
FROM students s
CROSS JOIN courses c;

Every student paired with every course. If you have 3 students and 5 courses, you get 15 rows. Useful for generating combinations.

Self join — table joined with itself

SELECT e.name AS employee, m.name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;

A self join treats the same table as two different tables — useful for hierarchical data like org charts.

Join conditions with ON vs WHERE

-- Filter during join (preferred)
SELECT s.name, e.course
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id AND e.course = 'Math';

-- Filter after join
SELECT s.name, e.course
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.course = 'Math';

In LEFT JOIN, putting conditions in ON vs WHERE changes the result. Conditions in ON keep unmatched rows. Conditions in WHERE filter them out.

Multiple joins

SELECT s.name, e.course, t.teacher_name
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN teachers t ON e.teacher_id = t.id
WHERE e.course = 'Math';

Chain joins by adding more INNER JOIN clauses. Each join connects on a specific condition.

Aggregate with joins

SELECT s.name, COUNT(e.course) AS course_count
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
GROUP BY s.name;

LEFT JOIN ensures all students appear, even those with zero enrollments (count = 0).

Join types summary

Join	Returns
INNER JOIN	Only matching rows from both tables
LEFT JOIN	All rows from left + matching from right
RIGHT JOIN	All rows from right + matching from left
FULL JOIN	All rows from both tables
CROSS JOIN	Every combination of rows
SELF JOIN	Table joined with itself

Common join patterns

Finding unmatched records

-- Students not enrolled in any course
SELECT s.name
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.student_id IS NULL;

Many-to-many relationships

-- Students and their courses
SELECT s.name, c.name AS course
FROM students s
INNER JOIN student_courses sc ON s.id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.id
ORDER BY s.name, c.name;

Mini Practice

1. Write a LEFT JOIN that shows all students and their enrollments
2. Find students who are NOT enrolled in any course
3. Count the number of courses each student is enrolled in
4. Write a query joining three tables
5. Use a CROSS JOIN to generate all combinations of sizes and colors

Next: aggregate functions and GROUP BY →