Finding missing rows

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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?

Options
• SELECT t.name FROM trainers t LEFT JOIN codemon c ON c.trainer_id = t.trainer_id WHERE c.codemon_id IS NULL;
• SELECT t.name FROM trainers t INNER JOIN codemon c ON c.trainer_id = t.trainer_id WHERE c.codemon_id IS NULL;
• SELECT t.name FROM trainers t LEFT JOIN codemon c ON c.trainer_id = t.trainer_id WHERE c.codemon_id IS NOT NULL;
• SELECT t.name FROM trainers t LEFT JOIN codemon c ON c.trainer_id = t.trainer_id WHERE c.codemon_id = NULL;

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.

Items to match
• FROM trainers t LEFT JOIN codemon c ON ... WHERE c.codemon_id IS NULL
• FROM codemon c LEFT JOIN trainers t ON ... WHERE t.trainer_id IS NULL
• FROM trainers t INNER JOIN codemon c ON ... WHERE c.codemon_id IS NULL
• FROM trainers t LEFT JOIN codemon c ON ... WHERE c.codemon_id IS NOT NULL
Definitions
• Jordan
• Plantachu
• No row at all
• The 7 real pairs

Exercise 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';
Options
• 4
• 2
• 7
• 8

Hint : 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.

Starter code
-- Write your query below
Back to the course