Connect tables

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.

Ask the Tool Library which tools have never been borrowed and an INNER JOIN cannot answer. It keeps only the pairs that match, and a tool with no loans has no pair. The tool disappears from the result, and nothing tells you it did.

A LEFT JOIN keeps every row from the table on its left, matched or not. When a tool has no loan, the loan columns come back as NULL. Those NULLs are not missing data. They are the answer.

SELECT t.name, t.category
FROM tools t
LEFT JOIN loans l ON l.tool_id = t.id
WHERE l.id IS NULL
ORDER BY t.name;

So the question 'which tools were never borrowed' becomes 'which tools have a NULL where the loan's id should be'. The test is WHERE l.id IS NULL, on the key of the right-hand table. Testing a column that can be NULL for other reasons, such as return_date, asks a different question.

The gap test works on any pair of tables with a key between them: members with no loans, branches with no workshops, runners who registered for the marathon and never finished a race.

Count the rows of tools LEFT JOIN loans and you get 130: one row for each of the 120 loans, plus one row for each of the 10 tools that were never borrowed. Count the rows of the INNER JOIN and you get 120. Neither number is the number of tools, which is 45.

Predict before you run. If you expect 45 and see 130, the query is pairing rows, not listing tools, and you have learned something before the dashboard lies to a manager.

When you count after a LEFT JOIN, COUNT(*) counts the gap rows too, and COUNT(l.id) does not, because it skips NULL. One of them answers 'how many loans has this tool had', and it is the one that can say zero.

The director asks for every branch and its number of garden tools, including the branches that have none. You write branches LEFT JOIN tools, add WHERE t.category = 'Garden', count, and five branches come back. Cloquet owns no garden tools, so after the LEFT JOIN its tool columns are NULL, and NULL = 'Garden' is not true. The WHERE threw away the very row the LEFT JOIN had kept.

A condition about the right-hand table belongs in ON when every left-hand row must survive: LEFT JOIN tools t ON t.branch_id = b.id AND t.category = 'Garden'. Now the pairing itself is restricted to garden tools, a branch with none still gets its one row of NULLs, and COUNT(t.id) reports zero for it.

SELECT b.id AS branch_id, b.name AS branch, COUNT(t.id) AS garden_tools
FROM branches b
LEFT JOIN tools t ON t.branch_id = b.id AND t.category = 'Garden'
GROUP BY b.id, b.name
ORDER BY b.id;

Group by the branch's id and show its name, so the output is explicitly one row per branch. Count the right-hand key, never the star. Then check the row count against the branches table: six branches, six rows, every time.

Nothing says the two sides of a join must be different tables. The staff table has a supervisor_id that points at another row of staff. Give the table two aliases and it becomes two copies: e for the employee, m for the manager that e.supervisor_id points at. The aliases name roles, not two tables stored twice.

The bridge is ON m.id = e.supervisor_id. Write ON m.id = e.id instead and every person becomes their own supervisor, fourteen rows of nonsense that run without a complaint.

SELECT e.id AS employee_id, e.first_name AS employee, m.first_name AS supervisor
FROM staff e
LEFT JOIN staff m ON m.id = e.supervisor_id
ORDER BY e.id;

One row of staff has a NULL supervisor_id: the director, at the top of the chart. An INNER JOIN drops her, because NULL matches nothing. If the chart must show every employee, the employee copy goes on the left of a LEFT JOIN and the director keeps her row with a NULL where a supervisor's name would be.

A loan row names a member, a tool and a staff member by their keys. When each output row should describe a loan, start from loans and follow each foreign key to its parent: JOIN members on l.member_id, JOIN tools on l.tool_id, JOIN staff on l.staff_id. Three joins, one row per loan, 120 rows. The hub is the table whose rows the report is about.

Resist the shortcut. Members and tools both carry a branch_id, so you could pair them on it. That pairs every member with every tool at the same branch, 341 rows that mean nothing: a shared branch is not a loan. For every join you write, name the fact that makes the pair real.

The Tool Library has three different branches in one loan: the branch that owns the tool, the home branch of the member, and the branch where the staff member works. They are three relationships, three joins to the branches table under three aliases. The director's replacement-value report attributes value to the branch that owns the tool. Pick the branch the question is about, not the one that is easiest to reach.

Grandma's Marathon stores the official finisher count for each race. Ten marathons, and their counts add to 63,218. Join the results table to get the year labels, sum the same column, and the total becomes 2,442,010. Nothing failed. Each race's count was repeated once per result row, and SUM added every repeat.

SELECT s.branch_id, ROUND(SUM(s.hourly_wage), 2) AS wage_rate_sum
FROM staff s
GROUP BY s.branch_id
ORDER BY s.branch_id;

The same thing happens at the Tool Library. A staff member's hourly wage is one fact per person. Join staff to loans and that wage appears once per checkout; sum it and Priya's wage is counted thirteen times. The total grows every time someone borrows a hammer, which is not what a wage total should do.

The fix is not SUM(DISTINCT). Two different employees can earn the same wage and both must count. The fix is grain: aggregate each fact at the level where it is stored once, and join only after. Sum wages from staff alone. Count loans from loans alone. Combine the two summaries if the question needs both.

The habit that catches this is a single comparison: compute the total from the parent table by itself, then from the join. If they differ, you have multiplied something.