Joining tables · Lesson 10 of 20

LEFT JOIN: leave nobody out

Keep every row of the first table and find what has no match.

9 minutes2 games

LEFT JOIN keeps every row of the left table, even those with no match. The columns from the right side are NULL for those rows.

SQL

SELECT s.name, COUNT(e.course_id) AS courses
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
GROUP BY s.id, s.name
ORDER BY courses DESC, s.name;

Youssef and Hana appear with 0. Note we counted COUNT(e.course_id), not COUNT(*): their row exists, but course_id is NULL, and COUNT skips NULL.

Who has no enrollments at all?

SQL

SELECT s.name
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
WHERE e.student_id IS NULL;

LEFT JOIN: every student, with NULL beside those without enrollments

WHERE e.student_id IS NULL

Only the unmatched remain: Youssef and Hana

This pattern is an anti-join, a favourite interview question.

Spot the bug

We want every student, with the courses they're more than halfway through. But Youssef and Hana vanished. Why?

Exercise

Get the title of courses that no student is enrolled in.

Expected result:

title
C++

SQL

SELECT c.title
FROM courses c

Learn more from

Played 0 of 2 games

SQL