JOINs

Chapter 5 12 code blocks 4274 words

Chapter 1 explained why data is split across tables: each fact is stored exactly once, so it cannot disagree with itself. JOINs are the other half of that bargain — they put the pieces back together for the duration of one query.

This is the chapter where students either "get" SQL or quietly stop understanding it, so go slowly and always draw the two tables before writing the query.

Why JOINs exist

Look at what enrollments actually stores:

SELECT * FROM enrollments LIMIT 3;

enrollment_idstudent_idcourse_idteacher_idenrolled_atgradestatus

11142025-09-0592completed 21222025-09-0588completed 31512026-02-03NULLactive

Nobody can read this. It says "1 studied 1 with 4" — three numbers and no meaning. A human needs "Aziza studied Python Basics with Sardor Yoqubov". Turning the first table into the second sentence is the entire job of a JOIN.

INNER JOIN

INNER JOIN keeps only rows that matched on both sides. It is the default — plain JOIN means INNER JOIN.

SELECT
    s.first_name,
    s.last_name,
    e.grade,
    e.status
FROM enrollments e
INNER JOIN students s ON e.student_id = s.student_id
ORDER BY e.enrollment_id
LIMIT 5;

5 rows

1 ms

ON, USING, and the old comma syntax

-- 1. ON — always works, and the only option when column names differ
SELECT s.first_name, e.grade
FROM students s JOIN enrollments e ON s.student_id = e.student_id;

-- 2. USING — shorter, but only when BOTH columns have the same name
SELECT s.first_name, e.grade
FROM students s JOIN enrollments e USING (student_id);

-- 3. The old comma syntax — an implicit inner join. Do not teach this.
SELECT s.first_name, e.grade
FROM students s, enrollments e
WHERE s.student_id = e.student_id;

Result 1

… first 15 of 2500 rows

2500 rows

Result 2

… first 15 of 2500 rows

2500 rows

Result 3

… first 15 of 2500 rows

2500 rows

4 ms

:::gotcha ⚠️ Why the comma syntax is banned in modern code Forget the WHERE line and you do not get an error — you get a CROSS JOIN: every student paired with every enrollment, 10 × 20 = 200 rows of nonsense. With explicit JOIN ... ON, the join condition sits next to the join and cannot be lost. There is also no way to write an outer join with commas. Use JOIN ... ON. :::

LEFT JOIN

LEFT JOIN keeps every row of the left table, matched or not. Where there was no match, the right table's columns come back as NULL.

This is exactly the fix for the missing course in Chapter 4:

SELECT
    c.title,
    COUNT(e.enrollment_id) AS enrollments,
    ROUND(AVG(e.grade), 1) AS avg_grade
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_id, c.title
ORDER BY enrollments DESC;

15 rows

1 ms

Deep Learning0NULL

Deep Learning finally appears, with 0. That single row is usually the most valuable line in the whole report — the course nobody signed up for is exactly what management needs to see.

The same shape works for the NULL teacher:

SELECT
    e.enrollment_id,
    COALESCE(t.full_name, '(not assigned)') AS teacher
FROM enrollments e
LEFT JOIN teachers t ON e.teacher_id = t.teacher_id
WHERE e.enrollment_id IN (18, 19, 20);

3 rows

0 ms

RIGHT and FULL OUTER JOIN

RIGHT JOIN is the mirror image: it keeps every row of the right table. FULL OUTER JOIN keeps every row of both, filling NULLs on whichever side is missing.

FULL OUTER JOIN is rare in analytics but genuinely useful for reconciliation — comparing two systems that should contain the same records and finding what is missing on either side. Where it is unsupported, emulate it with two LEFT JOINs and a UNION (Chapter 9).

Anti-join: rows with no match

"Which X has no Y?" is one of the most common analytics questions there is: customers who never ordered, products never sold, students not enrolled. The pattern is always the same — LEFT JOIN, then keep the rows where the right side came back NULL.

SELECT c.course_id, c.title
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
WHERE e.enrollment_id IS NULL;

1 row

1 ms

-- (a) LEFT JOIN + IS NULL — usually the fastest
SELECT c.title FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
WHERE e.enrollment_id IS NULL;

-- (b) NOT EXISTS — clearest intent, NULL-safe
SELECT c.title FROM courses c
WHERE NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.course_id = c.course_id);

-- (c) NOT IN — only safe because enrollments.course_id is NOT NULL. Fragile.
SELECT c.title FROM courses c
WHERE c.course_id NOT IN (SELECT course_id FROM enrollments);

Result 1

1 row

Result 2

1 row

Result 3

1 row

2 ms

:::tip 💡 Pick the column you test carefully In pattern (a), test a column of the right table that can never be NULL in real data — its primary key is the safe choice. If you test a nullable column such as e.grade, you will also catch enrollments that exist but are ungraded, and the answer will be wrong. :::

Joining three or more tables

JOINs chain: the result of one join is a table, which is then joined to the next. Start from the junction table (enrollments) and hang the lookup tables off it.

SELECT
    s.first_name || ' ' || s.last_name    AS student,
    c.title                               AS course,
    COALESCE(t.full_name, '(none)')       AS teacher,
    e.grade,
    e.status
FROM enrollments e
JOIN      students s ON e.student_id = s.student_id
JOIN      courses  c ON e.course_id  = c.course_id
LEFT JOIN teachers t ON e.teacher_id = t.teacher_id
ORDER BY e.enrollment_id
LIMIT 6;

6 rows

0 ms

CROSS JOIN and SELF JOIN

CROSS JOIN

Every row of A with every row of B — no condition. The result has m × n rows.

SELECT COUNT(*) FROM students CROSS JOIN courses; -- 10 × 6 = 60 It is almost always an accident (a forgotten ON), but it has one genuine use: building a complete grid to report zeros against. "Every course, every month, even the empty combinations" is impossible with GROUP BY alone — you must generate the grid first, then LEFT JOIN the real data onto it.

SELF JOIN

A table joined to itself, using two different aliases so the engine can tell the copies apart. Use it to compare rows within one table.

-- Pairs of students who live in the same city
-- (a.student_id < b.student_id prevents self-pairs AND mirror duplicates)
SELECT
    a.first_name AS student_a,
    b.first_name AS student_b,
    a.city
FROM students a
JOIN students b ON a.city = b.city AND a.student_id < b.student_id
ORDER BY a.city;

… first 15 of 6550 rows

6550 rows

4 ms

student_astudent_bcity

BekzodZarinaSamarkand AzizaJasurTashkent AzizaOtabekTashkent JasurOtabekTashkent

Sevara is absent because her city is NULL, and NULL = NULL is UNKNOWN — the NULL rule from Chapter 1 applies inside ON exactly as it does inside WHERE.

Fan-out: the silent row-multiplication bug

This is the most expensive JOIN mistake, because the query runs perfectly and returns a plausible number that is simply wrong.

SELECT COUNT(*) FROM students;                                  -- 10

SELECT COUNT(*) FROM students s
JOIN enrollments e ON s.student_id = e.student_id;              -- 20  ← !!

SELECT COUNT(DISTINCT s.student_id) FROM students s
JOIN enrollments e ON s.student_id = e.student_id;              -- 10

Result 1

1 row

Result 2

1 row

Result 3

1 row

1 ms

Joining to the "many" side duplicated every student row — once per enrollment. Counting is now counting enrollments, not students. With money, it gets worse:

-- What is our total teacher salary bill?
SELECT SUM(salary) FROM teachers;
-- 36,500,000  — correct

-- Same question, but someone joined enrollments to filter by course
SELECT SUM(t.salary) FROM teachers t
JOIN enrollments e ON t.teacher_id = e.teacher_id;
-- 175,600,000  — nearly 5× too high, and no error anywhere

Result 1

1 row

Result 2

1 row

1 ms

Nodira's salary was counted 6 times (she appears in 6 enrollments), Rustam's 5 times, and so on. The report looks professional and is off by 139 million.

ON vs WHERE in an outer join

In an INNER JOIN it makes no difference where you put a condition. In a LEFT JOIN it changes the answer completely, and this is a top-tier interview question.

-- (A) condition in ON: filters the RIGHT table BEFORE joining.
--     Every course is still kept.
SELECT c.title, COUNT(e.enrollment_id) AS completed
FROM courses c
LEFT JOIN enrollments e
       ON c.course_id = e.course_id AND e.status = 'completed'
GROUP BY c.course_id, c.title;

-- (B) condition in WHERE: filters the RESULT AFTER joining.
--     Courses with no completed enrollment are deleted — the LEFT JOIN
--     silently became an INNER JOIN.
SELECT c.title, COUNT(e.enrollment_id) AS completed
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
WHERE e.status = 'completed'
GROUP BY c.course_id, c.title;

Result 1

15 rows

Result 2

14 rows

2 ms

Deep Learning0row disappears

:::info 📌 The rule In a LEFT JOIN: conditions on the right table go in ON. Conditions on the left table go in WHERE. Putting a right-table condition in WHERE turns your outer join back into an inner join — except for IS NULL, which is the anti-join pattern and is deliberate. :::

Practice set

-- 1. Every student with the number of courses they take (including zero)
SELECT s.first_name, COUNT(e.enrollment_id) AS courses
FROM students s LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id, s.first_name ORDER BY courses DESC;

-- 2. Teachers and how many enrollments they handle
SELECT t.full_name, t.subject, COUNT(e.enrollment_id) AS lessons
FROM teachers t LEFT JOIN enrollments e ON t.teacher_id = e.teacher_id
GROUP BY t.teacher_id, t.full_name, t.subject ORDER BY lessons DESC;

-- 3. Which students take 'SQL for Analytics'?
SELECT s.first_name, s.last_name, e.grade
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses  c ON e.course_id  = c.course_id
WHERE c.title = 'SQL for Analytics' ORDER BY e.grade DESC;

-- 4. Courses nobody enrolled in (anti-join)
SELECT c.title FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
WHERE e.enrollment_id IS NULL;

-- 5. Revenue per course category (price counted once per enrollment)
SELECT c.category, COUNT(*) AS enrollments, SUM(c.price) AS revenue
FROM enrollments e JOIN courses c ON e.course_id = c.course_id
GROUP BY c.category ORDER BY revenue DESC;

-- 6. Average grade per city (students who have grades)
SELECT COALESCE(s.city,'(unknown)') AS city,
       COUNT(e.grade) AS graded, ROUND(AVG(e.grade),1) AS avg_grade
FROM students s JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.city ORDER BY avg_grade DESC;

-- 7. Full readable enrollment report
SELECT s.first_name || ' ' || s.last_name AS student, c.title AS course,
       COALESCE(t.full_name,'(none)') AS teacher, e.grade, e.status
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses  c ON e.course_id  = c.course_id
LEFT JOIN teachers t ON e.teacher_id = t.teacher_id
ORDER BY student, course;

-- 8. Students in the same city (self join, one direction only)
SELECT a.first_name AS a, b.first_name AS b, a.city
FROM students a JOIN students b
  ON a.city = b.city AND a.student_id < b.student_id;

Result 1

… first 15 of 300 rows

300 rows

Result 2

12 rows

Result 3

… first 15 of 186 rows

186 rows

Result 4

1 row

Result 5

5 rows

Result 6

9 rows

Result 7

… first 15 of 2500 rows

2500 rows

Result 8

… first 15 of 6550 rows

6550 rows

13 ms

Interview questions

Q: INNER JOIN vs LEFT JOIN?

INNER returns only rows that matched on both sides. LEFT returns every row of the left table, with NULLs where the right side had no match. Use LEFT whenever "zero" is a meaningful answer.

Q: After a LEFT JOIN, why does COUNT(*) give the wrong answer?

Because unmatched left rows still produce one output row (with NULLs on the right), and COUNT(*) counts rows. Count a non-nullable column from the right table instead: COUNT(e.enrollment_id).

Q: What is the difference between putting a condition in ON versus WHERE in a LEFT JOIN?

ON filters the right table before joining, so unmatched left rows survive. WHERE filters after joining, so it removes the NULL-filled rows and effectively converts the LEFT JOIN into an INNER JOIN.

Q: How do you find rows in A with no match in B?

A LEFT JOIN B ON ... WHERE B.primary_key IS NULL, or NOT EXISTS. Avoid NOT IN unless the column is guaranteed non-NULL (Chapter 3).

Q: What is a self join and when do you need one?

A table joined to itself under two aliases, to compare rows within the same table — employee/manager hierarchies, pairs sharing an attribute, consecutive events. Add a.id < b.id to avoid self-pairs and mirrored duplicates.

Q: Your SUM doubled after adding a JOIN. What happened?

Fan-out: the join matched multiple rows on the many side, duplicating the left rows and therefore the values being summed. Fix it by aggregating the many side first (a CTE or derived table), then joining — not with DISTINCT.

Q: Does JOIN order affect the result?

For inner joins, no — they are commutative and the optimiser reorders them freely. For outer joins, yes: an INNER JOIN placed after a LEFT JOIN can delete the NULL-extended rows the LEFT JOIN just preserved.

Q: What is a CROSS JOIN good for?

Generating a complete grid of combinations — every product × every month — which you then LEFT JOIN real data onto so the empty combinations report as 0 instead of disappearing.

Summary

• A JOIN glues matching rows side by side. The only difference between join types is what happens to unmatched rows.
• INNER JOIN = matches only. LEFT JOIN = all left rows + matches. RIGHT = mirror of LEFT. FULL = both sides. CROSS = every combination.
• Use explicit JOIN ... ON. Avoid comma joins and NATURAL JOIN.
• After a LEFT JOIN, count a right-table column, never COUNT(*), or zeros become ones.
• Anti-join = LEFT JOIN ... WHERE right_pk IS NULL — the answer to every "which X has no Y?" question.
• ON filters before, WHERE filters after. A right-table condition in WHERE silently converts a LEFT JOIN into an INNER JOIN.
• Once you go LEFT, stay LEFT — a later INNER JOIN undoes it.
• Fan-out is the dangerous bug: joining to the many side duplicates rows and inflates every SUM and COUNT. Check row counts before and after; aggregate first, then join.

Next: Chapter 6 — subqueries and CTEs, the tools that let you use the result of one query as the input to another.