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

Registered, never finished

Level 6: Transfer it · Exercise 1 of 1

Same idea as Never borrowed or not returned?, new data.

Grandma's Marathon keeps runners, one row per person who ever registered, and results, one row per finish. Which runners have no result at all? Return first_name and last_name, ordered by last name then first name.

Finish line: 24 rows · columns first_name, last_name · 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.