INTERACTIVE LECTURES

Answer questions across tables

Guided class activity · apply the ideas from the full SQL lessons.

Open the detailed lessons for this activity

Detailed lessons in this module

Open the full explanations, examples, and SQL exercises directly.

8Lecture 8: Multi-table Queries with JOINsPixar movies & box office · 3 exercises 9Lecture 9: OUTER JOINsBuildings & employees · 3 exercises 10Lecture 10: A Short Note on NULLsBuildings & employees with missing values · 2 exercises 11Lecture 11: Expressions across related tablesPixar movies & box office · 3 exercises 12Lecture 12: Order of Execution of a QueryPixar movies & box office · 2 exercises

Module overview & notes →All interactive lectures

Preparing for Lab 8 · After practice, try the team challenge.

Opening your saved work…

Distinguish no loan from an unreturned loan

Step 7 of 12 · Database: Twin Ports Tool Library

READ & UNDERSTAND

A LEFT JOIN gap test asks whether a matching row exists at all. Test loans.id IS NULL, since the primary key is populated for every real loan. Testing return_date IS NULL asks a different question: it also accepts real loans that have not been returned.

NOT EXISTS provides another way to ask for no matching row. The inner query refers to the outer tool ID and succeeds when a matching loan exists; NOT reverses that existence test. It avoids multiplying the outer tool rows.

An unused tool is a finding about this snapshot, not a reason by itself to discard it. Consider its branch opening date, purpose and replacement cost before recommending action.

Worked example

SELECT t.id AS tool_id, t.name AS tool_name FROM tools AS t LEFT JOIN loans AS l ON l.tool_id = t.id WHERE l.id IS NULL ORDER BY t.id;

The LEFT JOIN example rejects all real matches and keeps only tools with no recorded loan.

Both correct forms should return the same tool IDs, not merely the same number of rows.

Your question

Using NOT EXISTS, return tool_id and tool_name for tools with no loan record, ordered by tool id. Compare the ID set with the worked LEFT JOIN example.

Predict the result

Explain your evidence

How could return_date IS NULL make a tool that is currently borrowed appear never borrowed?

Try a query to unlock the worked-answer discussion.

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Query results

Predict the result, then run your query.

Read-only activity · One statement per run · First 200 rows displayed · Checks compare the complete bounded result, not just this preview.

Explore this query freely in the playground ↗

Check the save status above. Account-saved work is available in My Progress. Explanations are for discussion, not automatically graded. No prerequisite locks.