JOINs

Real data is split across tables — customers in one, their orders in another — so each fact is stored once and corrected once. Putting the pieces back together at query time is one clause: JOIN … ON.

Two tables, one link

CREATE TABLE customers (id INTEGER, city TEXT);
CREATE TABLE orders (ref TEXT, customer_id INTEGER);

INSERT INTO customers VALUES (7, 'Lisbon'), (8, 'Oslo'), (9, 'Quito');
INSERT INTO orders VALUES ('A-1', 7), ('A-2', 9), ('A-3', 7), ('A-4', 99);

orders.customer_id holds a value meaning "the customer whose id is this" — the foreign key, the only thing tying the two tables together. Note the deliberate mess: customer 8 (Oslo) has ordered nothing, and order A-4 points at customer 99, who does not exist.

Naming, before the next query uses it. With two tables in play you write table.column — a dotted pair, table name then column name — for any column whose name exists in both, or SQLite stops with ambiguous column name: id. An alias after the table name shortens it: FROM orders o lets you write o.ref. FROM orders AS o is legal and identical; for tables the AS is conventionally dropped, unlike the column aliases of chapter 1.

INNER JOIN pairs the rows

SELECT orders.ref, customers.city
FROM orders
JOIN customers ON orders.customer_id = customers.id;

A-1|Lisbon
A-2|Quito
A-3|Lisbon

JOIN and INNER JOIN are two spellings of one thing — the word INNER is optional and usually left out, which is why every example here writes the short form.

Read ON as the matching rule: for each row of the first table, find the rows of the second where the condition holds, and emit one combined row per match. By hand: A-1 carries customer_id 7, so it pairs with the customers row whose id is 7 — Lisbon. A-2 carries 9 — Quito. A-3 carries 7 — Lisbon again: a row is reused as often as it matches.

Two things are missing, and neither is an error. Oslo never appears: no order points at customer 8. A-4 never appears: no customer has id 99. An inner join keeps only the rows that found a partner, on both sides, silently — so a join that "loses" rows usually means a foreign key pointing at nothing, not a bug in your SQL.

Which table you name first is free here: FROM customers JOIN orders ON … returns the same three pairs, with the columns in whatever order you SELECT. That symmetry is what LEFT JOIN gives up.

LEFT JOIN keeps the unmatched left rows

SELECT customers.city, orders.ref
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id;

Lisbon|A-1
Lisbon|A-3
Oslo|
Quito|A-2

Every row of the left table — the one named before the words LEFT JOIN — appears at least once. Oslo found no partner, so its right-hand columns come back NULL, which this runner prints as nothing after the separator. A-4 is still gone: customers is the left table, and the orphan sits on the right. Here the table order matters.

Counting the right-hand side

SELECT c.city, COUNT(o.ref)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.city
ORDER BY c.city;

Lisbon|2
Oslo|0
Quito|1

Swap COUNT(o.ref) for COUNT(*) and Oslo reports 1: the NULL-filled row still exists, COUNT(*) counts rows, COUNT(column) skips NULLs. On the right of a LEFT JOIN, count a column, never *.

ON promises nothing about order either: joined rows arrive however the engine found them. Sort them yourself, with a key from either table.

Common mistakes

• Matching the wrong pair of columns. ON o.id = c.customer_id reads plausibly but names columns that do not exist; the run halts with no such column: o.id and prints nothing.
• Dropping the sort. Every pair is correct and the order arbitrary; the grader compares line by line, so the right rows in the wrong order score zero.
• Selecting the columns in the other order. A-1|Lisbon and Lisbon|A-1 are different outputs; the spec fixes which comes first.
• Reaching for LEFT JOIN when the question asks for pairs. It adds a line per unmatched left row, with an empty second column (Oslo|) — a line nobody asked for.

Your exercise

Print every book next to its author's name, sorted by title, replacing the title-only query at the bottom of the editor. Both tables and their rows are given; you supply the pairing rule and the sort. Two of the given rows have no partner — one book, one author — and an inner join leaves both out.