Finding missing rows
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Finding what is missing
"Which trainers own no Codémon at all?" is a very common question, and LEFT JOIN answers it in two steps: keep every trainer, then keep only the rows where nothing was attached.
SELECT t.name AS trainer
FROM trainers t
LEFT JOIN codemon c ON c.trainer_id = t.trainer_id
WHERE c.codemon_id IS NULL;A real match always carries a Codémon with a codemon_id; only the unmatched rows have NULL there. The WHERE ... IS NULL keeps exactly those. This pattern is often called an anti-join.
Test a column that can never be NULL
Use the primary key of the right table in the IS NULL test. A column like capture_date could be NULL on a real match too, and you would get false positives.
Exercise 2 — Filter in ON or in WHERE?
The same pattern finds the wild Codémons — put codemon on the left and test the trainer's key:
SELECT c.name AS codemon
FROM codemon c
LEFT JOIN trainers t ON c.trainer_id = t.trainer_id
WHERE t.trainer_id IS NULL;Now a subtle but important point. Suppose you want every trainer, with only their fire Codémons attached. Putting the condition in ON keeps all four trainers:
SELECT t.name AS trainer, c.name AS fire_codemon
FROM trainers t
LEFT JOIN codemon c ON c.trainer_id = t.trainer_id AND c.type = 'fire'
ORDER BY t.name;Putting it in WHERE filters after the join: a row survives only if its Codémon really is a fire type. Jordan's all-NULL row fails the test NULL = 'fire', Alex's water and electric rows fail it too, and the LEFT JOIN silently behaves like an INNER JOIN:
SELECT t.name AS trainer, c.name AS fire_codemon
FROM trainers t
LEFT JOIN codemon c ON c.trainer_id = t.trainer_id
WHERE c.type = 'fire'
ORDER BY t.name;Rule of thumb
Conditions about the right table go in ON when you want to keep the unmatched left rows. WHERE is for filtering the final result (that's also where the IS NULL of an anti-join belongs).
Exercise 3 — Trainers without Codémon
Which query returns the trainers who own no Codémon?
Hint : An INNER JOIN has already dropped them, and = NULL is never true.
Exercise 4 — Wild Codémons
Complete the query so that it returns the Codémons that have no trainer.
Hint : Keep every Codémon, then keep the rows where no trainer was attached.
Exercise 5 — Queries and their results
Match each query with its result on our database.
FROM trainers t LEFT JOIN codemon c ON ... WHERE c.codemon_id IS NULLFROM codemon c LEFT JOIN trainers t ON ... WHERE t.trainer_id IS NULLFROM trainers t INNER JOIN codemon c ON ... WHERE c.codemon_id IS NULLFROM trainers t LEFT JOIN codemon c ON ... WHERE c.codemon_id IS NOT NULLJordanPlantachuExercise 6 — ON or WHERE
How many rows does this query return? (4 trainers; only Morgan and Taylor own a fire Codémon.)
SELECT t.name, c.name
FROM trainers t
LEFT JOIN codemon c ON c.trainer_id = t.trainer_id AND c.type = 'fire';4278Hint : The condition is in ON, so the left side stays complete; trainers without a fire Codémon get NULL.
Exercise 7 — No fire in the team
The fire gym is recruiting: it wants the trainers who do not own a single fire Codémon.
Write a query that returns the name of every trainer who owns no fire-type Codémon (trainers who own nothing at all count too), sorted by name. Remember where the type condition must go.
-- Write your query below