Lesson 19 of 30

Finding what is missing

Today at the studio

Rin has a hunch: "Which of our shows never got any merch? I bet we left money on the table." A normal join cannot show what is not there, but a left join can, with a small trick.

The idea

After a LEFT JOIN, rows with no match have NULL in every column of the right table. Filter for those with WHERE right_table.id IS NULL and you keep only the lonely rows.

This pattern is called an anti-join. Always test a column that can never be NULL in a real row, such as the id.

Example

SELECT characters.name
FROM characters
LEFT JOIN credits ON credits.show_id = characters.show_id
WHERE credits.staff_id IS NULL

Your turn

0 of 4 questions

Question 1 of 4

Which departments have no staff? Show name.