sql · level 2 · unit 2 · lesson 2
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
pairs each row with its match in another table.
Looking up ids by hand gets old fast. JOIN does it for you. Write pets JOIN owners in FROM, then says which columns must match: the pet's owner_id and the owner's id.
Each pet is paired with its owner, and the pair becomes one wide row. Name each column as table.column, like owners.name, because both tables have a name.
A plain JOIN is short for : it keeps only rows that found a match. Nibbles has no owner, so Nibbles vanishes from the result.
SELECT pets.name, owners.name AS owner
FROM pets
JOIN owners ON pets.owner_id = owners.id;Without ORDER BY, joined rows come back in whatever order is quickest for the database.
guess first
Six pets, four owners. How many pairs?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT COUNT(*) AS pairs
FROM pets
JOIN owners ON pets.owner_id = owners.id;your turn
Your turn. Show where each pet lives.
Type pets.species or pets.age in the blank. Each row then shows a pet, that detail, and the town its owner lives in.
result
Your answer appears here as a table.
fix it
Bubbles is Ana's fish, not Cleo's. Fix it!
result
Your answer appears here as a table.
- Look at
ON. Which pets column holds the owner's id? pets.idis the pet's own number. The link to the owner lives inpets.owner_id.- Change pets.id to pets.owner_id, then press run.
nailed it
you joined two tables into one. :D
JOIN ... ONpairs up rows whose columns match.- Write
table.columnwhen both tables share a column name. - A plain
JOINdrops rows that find no match.
new words in your collection
- JOIN
- ON
- INNER JOIN
This lesson isn’t saved. A free account remembers every lesson you finish.