sql · level 2 · unit 3 · lesson 2
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
LEFT JOIN plus IS NULL finds rows with no match.
Which customers have never ordered? Which dishes has nobody chosen? Questions about what is missing are some of the most useful a business can ask.
Start with a LEFT JOIN, so every customer stays. Customers with no orders get NULL in every orders column. Then WHERE o.id IS NULL keeps only those rows.
This pattern is called an . Test a column that is never empty in a real match, like the id.
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;= NULL never matches anything, so always write IS NULL.
guess first
Which dish has nobody chosen?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT d.dish
FROM dishes d
LEFT JOIN orders o ON o.dish_id = d.id
WHERE o.id IS NULL;your turn
Your turn. Find the owners with no pets.
Fill the first blank with the join that keeps every owner. Fill the second with a pets column that is only empty when there is no match.
result
Your answer appears here as a table.
fix it
Nibbles has no owner, but nothing shows. Fix it!
result
Your answer appears here as a table.
- Which rows does a plain
JOINkeep? Could any of them have an emptyo.id? - A plain
JOINdrops Nibbles beforeWHEREeven looks. You need a join that keeps every pet. - Change JOIN to LEFT JOIN, then press run.
nailed it
you found what was missing. :D
LEFT JOINplusWHERE ... IS NULLfinds rows with no match.- Test a column that is never empty in a real match.
- It needs
LEFT JOIN. A plainJOINfinds nothing.
new words in your collection
- anti-join
This lesson isn’t saved. A free account remembers every lesson you finish.