sql · level 2 · unit 3 · lesson 1
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
keeps every row from the first table.
A plain JOIN drops rows that find no match, so Nibbles vanished. Sometimes that hides the most important row. LEFT JOIN keeps every row from the : the one written first.
When a left row finds no match, it stays anyway, and every column from the other table is NULL. Nibbles comes back, with NULL for the owner.
Which table goes first matters. pets LEFT JOIN owners keeps every pet. owners LEFT JOIN pets keeps every owner, Dev included.
SELECT p.name AS pet, o.name AS owner
FROM pets p
LEFT JOIN owners o ON p.owner_id = o.id;Only the left table is kept whole. Here Dev, an owner with no pets, still vanishes.
guess first
Owners go first this time. What comes back?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT o.name AS owner, p.name AS pet
FROM owners o
LEFT JOIN pets p ON p.owner_id = o.id
ORDER BY o.name, p.name;your turn
Your turn. Choose which table is kept whole.
Type pets in one blank and owners in the other, in either order. Run it both ways and spot who comes back with NULL.
result
Your answer appears here as a table.
fix it
Dev lives in Bath, but is missing. Fix it!
result
Your answer appears here as a table.
- Bath is not York, so the
WHEREkeeps Dev. Which other part of the query drops Dev? - A plain
JOINdrops every owner who has no matching pet. - Change JOIN to LEFT JOIN, then press run.
nailed it
nobody got left out this time. :D
LEFT JOINkeeps every row of the left table.- With no match, the other table's columns are
NULL. - The table written first is the one kept whole.
new words in your collection
- LEFT JOIN
- left table
This lesson isn’t saved. A free account remembers every lesson you finish.