Lesson 12 of 17 · Combining Tables
LEFT JOIN
Keep every row from one table with SQL LEFT JOIN. Find records with no match, count zeros correctly and understand the difference from INNER JOIN.
- 8 min read
- Beginner
- Reviewed
- How a left join differs from an inner join
- How to find rows that have no match in another table
- How to count per group and still include zeros
- A trap that silently turns a left join into an inner join
The rows an inner join hides
An inner join keeps only rows that match on both sides. In the last lesson, that made two students disappear, because they have no enrollments.
But questions about what is missing are often the most valuable ones. Which students have not started a course? Which products have never been sold? Which customers have never ordered?
A left join keeps every row from the first table, whether it has a match or not.
SELECT s.name, e.course_id, e.score FROM students AS s LEFT JOIN enrollments AS e ON e.student_id = s.id ORDER BY s.id;
Scroll to the bottom of the result. Omar Hassan and Sofia Rossi are there, with NULL in the enrollment columns. The database had nothing to put there, so it filled the gap with NULL.
Left means "the first table"
The table written before LEFT JOIN is the left table. All of its rows are kept. The table written after is the right table. Its rows appear only where they match.
FROM students LEFT JOIN enrollmentskeeps every student.FROM enrollments LEFT JOIN studentskeeps every enrollment.
So with a left join, the order of the tables matters.
Finding rows with no match
Rows without a partner have NULL in the right table's columns. Filter for that, and you get exactly the unmatched rows.
SELECT s.name FROM students AS s LEFT JOIN enrollments AS e ON e.student_id = s.id WHERE e.id IS NULL;
These are the students who have not enrolled in anything. Test the right table's primary key for NULL, because a primary key can never be missing in a real row.
The same pattern works in the other direction: which courses have no students?
SELECT c.title FROM courses AS c LEFT JOIN enrollments AS e ON e.course_id = c.id WHERE e.id IS NULL;
Counting, including the zeros
How many courses is each student taking? With an inner join, students with no enrollments vanish from the answer. With a left join they stay, and show a count of zero.
SELECT s.name, COUNT(e.id) AS courses FROM students AS s LEFT JOIN enrollments AS e ON e.student_id = s.id GROUP BY s.id, s.name ORDER BY courses DESC, s.name;
Look carefully at the aggregate: it is COUNT(e.id), not COUNT(*).
COUNT(*)counts rows. An unmatched student still has one row, so the count would be 1. That is wrong.COUNT(e.id)counts rows where an enrollment exists. For an unmatched student that is 0. That is right.
Inner or left?
INNER JOIN | LEFT JOIN | |
|---|---|---|
| Rows kept | Only those with a match on both sides | Every row of the left table |
| Unmatched rows | Dropped | Kept, with NULL on the right |
| Use it when | You only care about matched pairs | The left table must stay complete, or you want what is missing |
The trap: filtering the right table in WHERE
Suppose you want every student, together with any scores of 90 or more.
SELECT s.name, e.score FROM students AS s LEFT JOIN enrollments AS e ON e.student_id = s.id WHERE e.score >= 90 ORDER BY s.name;
Most students have gone. WHERE runs after the join and removes every row where the score is NULL or below 90, including all the unmatched students. The left join has quietly become an inner join.
To keep every student, move the condition into ON. It then decides which enrollments may match, not which rows survive.
SELECT s.name, e.score FROM students AS s LEFT JOIN enrollments AS e ON e.student_id = s.id AND e.score >= 90 ORDER BY s.name;
All twelve students are back. Those without a high score show NULL.
Common mistakes
List every course with its number of enrolled students. A course with nobody enrolled must appear with a count of 0.
-- Show EVERY course title with the number of students enrolled in it, -- including courses with no students (they must show 0). -- Columns: title, students
In FROM students LEFT JOIN enrollments ... , what happens to a student with no enrollments?
Summary
- A left join keeps every row of the first table.
- Unmatched rows have
NULLin the second table's columns. WHERE right_table.id IS NULLfinds rows with no match.- With a left join, count a column from the right table, not
*. - A
WHEREcondition on the right table removes unmatched rows. Put it inONto keep them.
Next: joining three tables and more.
Finished reading? Tick it off to track your progress.