Practise · Never borrowed or not returned?

Opening your saved work…

The reading

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;

Ten tools, each with a NULL where a loan would be. Open in the playground

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.

Rule of thumb. To find what has no match, keep every row with a LEFT JOIN and test the other table's key for NULL.

Going deeper (optional, not on the exam)

RIGHT JOIN and FULL OUTER JOIN exist in other engines. The browser engine refuses them, and so does the course: write the table you want to keep on the left. A FULL join is two LEFT JOINs stitched together with UNION.

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.

Never borrowed or not returned? · LEFT JOIN and the unmatched row · level 1 of 6

Ten tools nobody has borrowed

Level 1: Watch it answered · Exercise 1 of 1

Which tools have never been borrowed? Watch the query answer it, then run it yourself.

Data: Twin Ports Tool Library.

Why it works, clause by clause

FROM tools t: start from the table whose rows must all survive. Every tool, borrowed or not.
LEFT JOIN loans l ON l.tool_id = t.id: pair each tool with its loans. A tool with no loans still gets one row, with NULL in every loan column.
WHERE l.id IS NULL: keep only the rows where the pairing found nothing. That is the gap.
ORDER BY t.name: a repeatable order, so the list reads the same every time.

From the reading: Never borrowed, or not returned?, paragraph 2

I would like help with this exercise

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;

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.