Joining tables

Two tables, one key

Data is split across tables on purpose: the authors table holds each person once, and every book points at its author with an id. A join puts the two back together for the length of one query, matching each book to the author row whose id it carries.

The matching condition goes in ON: JOIN authors ON authors.id = books.author_id. It says what makes a pair. Everything you know about filtering and ordering still applies to the joined result as if it were one table.

Aliases keep it readable

Once a query names two tables, every column reference must say which one it means. Short aliases are the habit that makes joins readable: FROM books b JOIN authors a ON a.id = b.author_id, and then b.title, a.name in the rest of the query.

The alias is also how the same table can be joined to itself later — twice, under two names — which is the trick behind any query about pairs or hierarchies.

INNER, and the rows that stayed out

A plain JOIN is an INNER JOIN: only pairs that match are kept. An author with no books simply disappears from the result — which is right when you asked about books, and wrong when you asked about authors.

LEFT JOIN keeps every row of the left table whatever happens, filling the right side with NULL where nothing matched. That is how "which authors have written nothing?" is asked: join, then look for the empty side with IS NULL.

What ON is doing

A join builds pairs. Conceptually the database takes every row of the left table with every row of the right — the Cartesian product — and keeps the pairs where the ON condition is true. That is why a missing or wrong ON produces a flood of rows: every pair passes.

Real databases do not build the whole product; they use an index or a hash table to find the matching rows directly. But the meaning is the product-and-filter, and that meaning is what tells you how many rows to expect.

A join does not merge the tables. It produces a new result table with the columns of both sides, which is why SELECT * on a join shows duplicate-looking id columns and why naming the columns you want is worth the typing.

One row can become many

If the right side has three rows for one left row, the join returns three rows. That is correct for a question about sales, and wrong for a count of books: COUNT(*) after joining books to sales counts sales, not books. COUNT(DISTINCT b.id) restores the unit you meant.

A many-to-many relationship cannot live in either table as a list. It needs a third table whose rows are pairs — one column for each side — and a query crosses it with two joins. The junction table is not red tape; it is the only shape that lets one book have many tags and one tag belong to many books.

Join order, and the one that is not symmetric

For inner joins, the order the tables are written in does not change the answer. A JOIN B and B JOIN A produce the same pairs, with the columns in a different order, and a chain of three can be joined in whichever sequence the database finds cheapest. Each ON is a statement about a pair of tables, and the result is every row that satisfies all of them at once.

A LEFT JOIN is the exception. It is not symmetric: the left table is the one kept whole, so swapping the two sides changes which rows survive. That is why an outer join is written in the order the question was asked — authors first when the report is about authors — rather than in whatever order reads neatly.

Books with their authors, and authors with no books

SELECT b.title, a.name AS author
FROM books b
JOIN authors a ON a.id = b.author_id
ORDER BY a.name, b.title;

SELECT a.name
FROM authors a
LEFT JOIN books b ON b.author_id = a.id
WHERE b.id IS NULL;

Try these next

• Add a second tag to one book and watch that book appear twice in the join.
• Find every tag that no book uses with a LEFT JOIN and IS NULL.
• Count the books per tag, including the tags with none.