LEFT JOIN
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — The rows INNER JOIN forgot
In the last lesson, Jordan (no Codémon) and Plantachu (no trainer) silently vanished from every INNER JOIN. Sometimes that is exactly what you want. Sometimes you need every trainer, even the ones with nothing to show.
LEFT JOIN keeps every row of the left table (the one after FROM), matched or not:
SELECT t.name AS trainer, c.name AS codemon
FROM trainers t
LEFT JOIN codemon c ON c.trainer_id = t.trainer_id
ORDER BY t.name, c.name;Jordan is back, with NULL in the codemon column: there was nothing to attach, so the columns coming from codemon are empty. Plantachu is still missing — it is on the right side, and only the left side is protected.
Same syntax, one word changes
LEFT JOIN is written exactly like INNER JOIN, with the same ON condition. You may also see LEFT OUTER JOIN: it is the very same thing, OUTER is optional.
Exercise 2 — NULL means "no match"
For an unmatched row, every column of the right table is NULL — not just the name. That has a famous consequence when you count:
SELECT t.name AS trainer,
COUNT(*) AS rows_per_trainer,
COUNT(c.codemon_id) AS codemon_count
FROM trainers t
LEFT JOIN codemon c ON c.trainer_id = t.trainer_id
GROUP BY t.name
ORDER BY t.name;COUNT(*) says Jordan has 1 — it counts the row produced by the join, even though it is all NULL on the Codémon side. COUNT(c.codemon_id) ignores NULL and gives the honest answer: 0.
Count a column of the right table
With a LEFT JOIN, always count a column of the right table (its primary key is perfect) instead of * — otherwise every unmatched row counts as 1.
Exercise 3 — Who gets the NULL?
In this query, which trainer appears with NULL as codemon?
SELECT t.name AS trainer, c.name AS codemon
FROM trainers t
LEFT JOIN codemon c ON c.trainer_id = t.trainer_id;JordanAlexPlantachuHint : Which trainer has no row in codemon pointing to them?
Exercise 4 — Keep every trainer
Complete the query so that it lists every trainer with their Codémons, including the trainers who own none.
Hint : The table that must be kept in full goes on the left, and the join must protect the left side.
Exercise 5 — Which table is on the left?
"Left" simply means "written first". Swap the two tables and you protect the other side — now every Codémon survives, and the wild Plantachu shows up with no trainer:
SELECT c.name AS codemon, t.name AS trainer
FROM codemon c
LEFT JOIN trainers t ON c.trainer_id = t.trainer_id
ORDER BY c.name;This time Jordan is the one missing: no Codémon points to Jordan, and only the Codémon side is protected.
Ask yourself first
Before writing a LEFT JOIN, decide which rows you refuse to lose. That table goes after FROM.
Exercise 6 — How many rows?
Our database has 4 trainers (Jordan owns nothing) and 8 Codémons (Plantachu is wild). Match each query with the number of rows it returns.
FROM trainers t INNER JOIN codemon c ON ...FROM trainers t LEFT JOIN codemon c ON ...FROM codemon c LEFT JOIN trainers t ON ...FROM trainers t LEFT JOIN codemon c ON ... WHERE t.name = 'Jordan'78, the extra row being Jordan8, the extra row being Plantachu1, Jordan's all-NULL rowExercise 7 — The COUNT trap
Why does this query report 1 for Jordan, who owns no Codémon?
SELECT t.name, COUNT(*) AS codemon_count
FROM trainers t
LEFT JOIN codemon c ON c.trainer_id = t.trainer_id
GROUP BY t.name;Hint : What does COUNT(*) count, and what does COUNT(c.codemon_id) count?
Exercise 8 — Zero, not one
Complete the query so that each trainer gets their real number of Codémons — 0 for Jordan.
Hint : Count something that is NULL when there is no match, and use the join that keeps Jordan.
Exercise 9 — Everyone counts
Professor Pi wants the full roster, including the trainers who are just starting out.
Write a query that returns every trainer's name as trainer and their number of Codémons as codemon_count (0 when they own none), sorted by codemon_count descending, then by trainer.
-- Write your query below