Join on a relationship, not a coincidence

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;

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.

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;

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.

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.