Questions Across Tables

Unit 1 split the world into linked drawers and promised the payoff would come. This is the payoff. Because the honest questions — the ones people actually ask — almost always span two drawers: "who borrowed what?" Members live in one table, books in another, and the loans between them are all pointers. Time to teach the librarian to follow them.

JOIN: two drawers, one counter

JOIN lays two tables side by side and matches their rows. You name the second table, and — the crucial part — you name how rows match:

SELECT loans.due, books.title
FROM loans
JOIN books ON loans.book_id = books.id;

Read the new clause slowly: "join in the books drawer, matching each loan to the book where the loan's book_id equals the book's id." The ON condition is the matching rule, and look at what it compares: a foreign key to the primary key it points at. The pointer you built in Unit 1 is the join. Every loan card said "see card 7 in the books drawer" — JOIN is the librarian actually walking over and getting card 7, for every card, in one pass.

due        | title
-----------+------------------
2026-08-10 | The Hobbit
2026-08-19 | A Wizard of Earthsea
2026-08-19 | The Hobbit

Each answer row is a matched pair — one loan's facts and its book's facts, fused. The Hobbit appears twice because two loans point at it: matches, not copies. Nothing was duplicated in storage; the pairing happens fresh on the counter, per question, then the trays go back.

One new spelling made this possible: with two drawers open, the name id alone would be ambiguous — whose id? So columns get their table's name in front, loans.book_id, books.id, the way you'd say "the loans drawer's book number." Verbose, and worth it.

The join key

The pair of columns in the ON clause is called the join key, and choosing it is not a creative act — the schema already decided. Which brings us to this lesson's classic stumble: joining on the wrong thing. Match on titles, names, anything human-readable, and you inherit every wobble names have — misspellings, duplicates, renames — as wrong or missing matches. The diagram from "Reading a Schema" is your map: every line on it is a join waiting to be used, foreign key to primary key. Join along the lines.

Everything composes

A JOIN's result is — as ever — a table, so every tool you own works on it. The full question "what is Sara still holding?":

SELECT books.title, loans.due
FROM loans
JOIN books ON loans.book_id = books.id
JOIN members ON loans.member_id = members.id
WHERE members.name = 'Sara Ali'
ORDER BY loans.due;

Two joins — loans to books, loans to members — then a filter, then a sort. Three drawers, one counter, one readable answer. This is SQL's entire trick, fully assembled: normalize the storage so nothing is copied, then join at ask-time so anything can be combined.

You now hold every clause this unit set out to teach. The finale doesn't add another — it teaches the harder skill of choosing: turning a plain English question into the right query, step by step.