LEFT JOIN
Keep every row from one table, even when it has no match, and find what's missing.
Lesson 26 of 33 · about 15 minutes
The lesson
In this lesson's practice database, two new rows have arrived. Jane Austen has joined as an author, but none of her books are listed yet. Mateo García from Madrid has signed up, but hasn't ordered anything.
An ordinary JOIN leaves both of them out, because they have nothing to match. Often that's exactly the information you want: which authors have no books listed, and which customers have never ordered?
LEFT JOIN keeps every row from the first (left) table, matched or not. Where there's no match, the other table's columns are filled with NULL:
SELECT authors.name, books.title
FROM authors
LEFT JOIN books ON books.author_id = authors.id;
Jane Austen appears once, with NULL as the title.
Find what's missing by keeping only those NULL rows:
SELECT authors.name
FROM authors
LEFT JOIN books ON books.author_id = authors.id
WHERE books.id IS NULL;
The order of the tables matters. The table that must keep every row goes first, straight after FROM.
Counting with zero. COUNT(books.id) counts only real matches, so an author with no books counts 0. COUNT(*) would count the NULL row and say 1.
- LEFT JOIN keeps every row from the first table, even with no match.
- Missing matches show up as NULL, so WHERE other.id IS NULL finds what's missing.
- Put the table that must keep all its rows first. Count with COUNT(other.id) to get 0 for no matches.