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.