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

Members who never borrowed

Level 2: Fill the blank · Exercise 1 of 1

The branch manager wants the members who have never taken a loan, so she can send them a welcome offer. One piece of the gap test is missing. Fill it in.

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

Data: Twin Ports Tool Library.

I would like help with this exercise

SELECT m.first_name, m.last_name FROM members m LEFT JOIN loans l ON l.member_id = m.id WHERE l.id ORDER BY m.last_name, m.first_name;

Blank 1 of 1: the test that finds a row with no match

Open in the playground

Query results

Check to see the result here.

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