Lesson 12 of 20
In progress
LEFT JOIN: keeping unmatched rows
Find rows with no match, place filters in the right clause, and avoid the WHERE condition that turns a left join back into an inner one.
INNER JOIN answers "which rows match". LEFT JOIN answers "show me everything on the left, and whatever matches on the right" — which is how you find the missing ones.
What you will learn
- Read a left join result with NULLs.
- Find rows with no match using
IS NULL. - Place filters in
ONorWHEREdepending on what should survive.
The result shape
Editable. Runs in your browser.
CREATE TABLE books (
id INTEGER PRIMARY KEY,
author_id INTEGER NOT NULL,
title TEXT NOT NULL,
year INTEGER
);
INSERT INTO authors (id, name) VALUES
(1, 'Ana'), (2, 'Ravi'), (3, 'Meera');
INSERT INTO books (id, author_id, title, year) VALUES
(1, 1, 'Small Systems', 2021),
(2, 1, 'Second Edition', 2024),
(3, 2, 'Quiet Data', 2019);
SELECT a.name, b.title
FROM authors AS a
LEFT JOIN books AS b ON b.author_id = a.id
ORDER BY a.name, b.title;
Press Run to see the output.
Four rows. Ana has two books, Ravi one, and Meera has a row with NULL in the title because no book matched. The left table is complete; the right side fills with NULLs where nothing exists.
That NULL is data, not an error. It is the signal that something is absent.
Counting with left joins
Editable. Runs in your browser.
Press Run to see the output.
COUNT(b.id) returns 0 for Meera, because COUNT(column) ignores NULL. COUNT(*) would return 1 for her, counting the placeholder row instead of a real book. This is the single most common bug in left-join reports.
The same applies to SUM: a sum over no matched rows is NULL, so wrap it in COALESCE(SUM(b.something), 0) when a number is required.
Finding rows with no match
Editable. Runs in your browser.
Press Run to see the output.
Read it as: keep every author, then keep only the rows where the right side failed to match. The result is Meera.
Any non-nullable column of the right table works as the test; b.id is conventional. Using a nullable column such as b.year would also return authors whose book exists but has no year, which is a different question.
The pattern generalises to "orphans" — rows whose parent is missing, found from the other direction:
Editable. Runs in your browser.
Press Run to see the output.
No rows here, because every book has an author. Run it after an import and it will find the rows that arrived before their parent.
The WHERE trap
Editable. Runs in your browser.
Press Run to see the output.
The result contains only Ana’s 2021 and 2024 books. Meera and Ravi are gone, because their b.year is NULL and NULL > 2020 is unknown. A condition on the right table in WHERE silently removes the unmatched rows, turning the left join into an inner join.
To keep every author and still filter the books:
Editable. Runs in your browser.
Press Run to see the output.
Now the condition is part of matching. Books that fail it are treated as absent, so Ravi and Meera appear with NULL titles. The choice between the two forms is a choice about which rows must survive, and it is worth stating in a comment when the query is not obvious.
Chaining left joins
Editable. Runs in your browser.
INSERT INTO reviews (id, book_id, stars) VALUES
(1, 1, 5), (2, 1, 4), (3, 2, 5);
SELECT a.name, b.title, COUNT(r.id) AS reviews
FROM authors AS a
LEFT JOIN books AS b ON b.author_id = a.id
LEFT JOIN reviews AS r ON r.book_id = b.id
GROUP BY a.id, a.name, b.id, b.title
ORDER BY a.name, b.title;
Press Run to see the output.
Each left join preserves everything to its left. Meera keeps a row with no book and no reviews, and the counts stay zero rather than vanishing. Grouping by b.id as well as a.id keeps each book’s reviews separate, which is what the detail column claims.
When LEFT JOIN is the wrong tool
If the question is "authors who have books", an inner join is the honest query: it says what it means and returns fewer rows for the planner to discard. Reaching for LEFT JOIN plus IS NOT NULL gives the same answer with more work and more ambiguity.
Use a left join when the absence matters: counting zero, listing everyone including the inactive, showing a profile with no orders yet.
Counting COUNT(*) after a left join to mean "how many related rows". It counts the placeholder row as one, so authors with no books report 1 instead of 0.
List every author with the number of books and the number of reviews, then list authors who have no reviews at all.
Show solution
Editable. Runs in your browser.
SELECT a.name
FROM authors AS a
LEFT JOIN books AS b ON b.author_id = a.id
LEFT JOIN reviews AS r ON r.book_id = b.id
WHERE r.id IS NULL;
Press Run to see the output.
COUNT(DISTINCT ...) is needed in the first query because the second join repeats each book once per review. The second query returns Ravi and Meera, since neither has a review; a review-free author with several books appears once because the WHERE filter removes every row with a review.
Recap
LEFT JOINkeeps every left row and fills the right side with NULLs when nothing matches.- Use
COUNT(right.id)andCOALESCEso zeros are real zeros. - A right-table condition in
WHEREdeletes unmatched rows; put it inONto keep them.
Next, subqueries let one query use the result of another, including inside a join.
Is LEFT JOIN slower than INNER JOIN?
What is a FULL OUTER JOIN?
Progress is saved in this browser only. No account, no email.