Never borrowed or not returned?

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.