meet it

A table alias keeps joins short and clear.

alias	table
p	pets
o	owners

Typing owners.name and pets.owner_id gets long. Give each table an alias straight after its name in FROM: pets p and owners o. Then the p. means pets and o. means owners.

Prefixes matter because name on its own is here: both tables have one. SQLite will not guess. It stops with ambiguous column name: name.

Clashing columns would also share a heading in the result, so give each one an alias with AS, like o.name AS owner.

SELECT p.name AS pet, o.name AS owner, o.town
FROM pets p
JOIN owners o ON p.owner_id = o.id
WHERE o.town = 'Leeds';

FROM pets AS p works too. Once a table has an alias, use the alias everywhere in that query.

guess first

Pets now goes by p. What happens?

Guess before you run it. Wrong guesses are how you learn, and nothing is marked.

SELECT pets.name, o.town
FROM pets p
JOIN owners o ON p.owner_id = o.id;

your turn

Your turn. Finish the join yourself.

Fill the two ON blanks with p.owner_id and o.id. Then type dog, cat or fish between the quotes.

Your code

result

Your answer appears here as a table.

fix it

Which cats live where? Fix the clash!

Broken code to fix

result

Your answer appears here as a table.

Read the error. Which column does it name?
Both tables have a name column, so SQLite cannot tell which one you mean.
Change name on line 1 to p.name, then press run.

nailed it

you gave your tables short names. :D

Write an alias straight after the table: FROM pets p.
Put a prefix, like p., on every shared column.
Once a table has an alias, use only the alias.

new words in your collection

prefix
ambiguous

This lesson isn’t saved. A free account remembers every lesson you finish.