Practise · Join on a relationship, not a coincidence

Opening your saved work…

The reading

Why tables are split, and why that costs you a JOIN

Open the schema panel and look at loans. It has a tool_id, not a tool name. If the name were copied onto every loan row, the day someone renames the Cordless Drill 18V you would have 9 rows to fix and a good chance of missing one.

So a fact is stored once, in the table it belongs to, and other tables point at it with a key. That is why real databases have many tables, and it is why the questions a manager asks need a JOIN: the tool's name and the loan's dates live in different places.

SELECT t.name, l.due_date
FROM loans l
JOIN tools t ON t.id = l.tool_id
LIMIT 3;

One line per loan, with the tool's name borrowed from the other table. Open in the playground

A JOIN is not a merge. It is a rule for pairing rows: this loan row goes with the tool row whose id matches its tool_id. Get the rule right and the pairing is right.

Rule of thumb. Store a fact once; connect it with a key.

Join on a relationship, not a coincidence

Read a FROM clause as a sentence. Start with tools, and for each tool, attach every branch row whose id equals that tool's branch_id. The ON clause is the bridge, and it always pairs a foreign key with the primary key it points to. That is the relationship you drew in the ERD; the join is how you walk across it.

An INNER JOIN keeps only the pairs that match. Forty-five tools each point at exactly one branch, so tools joined to branches on that key returns forty-five rows, one per tool, with the branch's name borrowed onto each. A row count that matches the table you started from is the first sign the pairing is right.

SELECT t.id AS tool_id, t.name AS tool_name, b.name AS owning_branch
FROM tools t
JOIN branches b ON b.id = t.branch_id
WHERE t.id <= 8
ORDER BY t.id;

Eight tools, each with the branch that owns it. All eight live at Lincoln Park. Open in the playground

Now join on the wrong columns. ON b.id = t.id pairs tool 2 with branch 2, tool 3 with branch 3, and stops at tool 6 because there are only six branches. Six rows come back, every one of them a real tool with a real branch name, and every one of them wrong. The engine cannot tell a key from a coincidence. Only you can, by naming the fact that makes the pair meaningful: this tool is owned by that branch.

Leave the ON clause off entirely and you get every tool paired with every branch: 45 times 6 is 270 rows. If a join returns far more rows than either table, look for a missing or wrong ON before you look anywhere else.

Rule of thumb. ON pairs a foreign key with the primary key it points to. Predict the row count, then check it.

Going deeper (optional, not on the exam)

You can also write a join with a comma and the condition in WHERE: FROM tools t, branches b WHERE b.id = t.branch_id. It runs. The course writes JOIN ... ON so the relationship is visible next to the table it connects, and so a forgotten condition shows up as a missing ON rather than a missing WHERE.

Aliases name tables; they do not create relationships

tools t and branches b give each table a short alias, and from then on every column is written t.column or b.column. This is not decoration. Both tables have an id and a name, so a bare SELECT name in that join fails with ambiguous column name. Qualify every column in a join, even the ones that happen to be unique, so the query still reads clearly when a column is added next year.

An alias is a nickname for a table in this one query. It does not tell the engine how the tables relate; only ON does that. A query with beautiful aliases and the wrong ON is still wrong.

Rule of thumb. In a join, every column wears its table's alias.

Join on a relationship, not a coincidence · INNER JOIN ON a key · level 6 of 6

Worldwide sales, movie by movie

Level 6: Transfer it · Exercise 1 of 1

Same idea as Join on a relationship, not a coincidence, new data.

The Pixar database keeps movies in one table and box-office figures in another, one row per movie, pointed at by movie_id. Return the five biggest films by worldwide sales: title and worldwide_sales (domestic plus international), largest first.

Finish line: 5 rows · columns title, worldwide_sales · in the order the question gives

Data: Pixar & Box Office.

I would like help with this exercise

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Open in the playground

Query results

Run your query to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.