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 1 of 6

How many tools does each branch own?

Level 1: Watch it answered · Exercise 1 of 1

One row per branch with its number of tools. Watch the count, then run it.

Data: Twin Ports Tool Library.

Why it works, clause by clause

FROM branches b: every branch must appear, so branches is the left table.
LEFT JOIN tools t ON t.branch_id = b.id: attach each branch's tools; a branch with none keeps one row of NULLs.
COUNT(t.id): count the tool key, which is NULL on the placeholder row, so a branch with no tools counts zero.
GROUP BY b.id, b.name: one row per branch. ORDER BY b.id for a repeatable order.

From the reading: Two counts, one honest, paragraph 3

I would like help with this exercise

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

Open in the playground

Query results

Run it to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.