The disappearing branch dashboard

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.