Practise · The disappearing branch dashboard

Opening your saved work…

The reading

Two counts, one honest

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.

Rule of thumb. After a LEFT JOIN, COUNT(*) counts the gaps; COUNT(the other table's key) counts the matches.

The disappearing branch

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;

Six rows. Cloquet shows 0 instead of disappearing. Open in the playground

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.

Rule of thumb. If every left row must survive, the right-side condition goes in ON, and the count goes on the right-side key.

Going deeper (optional, not on the exam)

Adding OR t.id IS NULL to the WHERE looks like a fix and is not: a branch that owns tools, none of them garden tools, has no NULL row to rescue and still vanishes.

The disappearing branch dashboard · COUNT(key) versus COUNT(*), and a condition in ON versus WHERE · level 6 of 6

Every runner from Two Harbors

Level 6: Transfer it · Exercise 1 of 2

Same idea as The disappearing branch dashboard, new data.

Grandma's Marathon: every runner from Two Harbors with the number of results on file, including anyone with none. Return first_name, last_name and races_run, most first, then last name A to Z. Decide first: JOIN or LEFT JOIN, and COUNT(*) or COUNT(s.id).

Finish line: 11 rows · columns first_name, last_name, races_run · in the order the question gives

Data: Grandma’s Marathon.

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.