INNER JOIN

A join combines rows from two tables where a condition holds. INNER JOIN — the default, and usually written just as JOIN — keeps only the rows where it holds on both sides.

The shape

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

         title         |      author
-----------------------+-------------------
 A Flight of Pigeons   | Ruskin Bond
 A Suitable Boy        | Vikram Seth
 An Equal Music        | Vikram Seth
 Batatyachi Chal       | P L Deshpande
 Breast Stories        | Mahasweta Devi
(5 rows)

Read it as: take each book, find the author whose id matches its author_id, and put them side by side.

FROM books b and JOIN authors a give the tables short aliases. Use them — b.title and a.name say which table each column came from, and without them a query over four tables is unreadable.

`ON` is a condition, not a keyword pair

JOIN authors a ON a.id = b.author_id
JOIN loans   l ON l.book_id = b.id AND l.returned_on IS NULL
JOIN books   b ON b.id = l.book_id AND b.copies > 1

Anything that evaluates to true or false. Matching on a key is overwhelmingly the common case, and it is not the only one.

Almost always you join on a foreign key to a primary key. If you find yourself joining on something else — a name, a date — stop and ask whether the schema is missing a relationship.

Inner means rows can disappear

The property that matters, and the source of most join bugs.

SELECT count(*) AS books FROM books;                              -- 40
SELECT count(DISTINCT b.id) AS books_with_loans
FROM books b JOIN loans l ON l.book_id = b.id;                    -- 39

Forty books; thirty-nine appear. One book has never been borrowed, so it has no matching row in loans and the inner join drops it entirely.

That is correct behaviour and it is silent. A report of "books and their borrowing counts" built on an inner join simply has no line for the book nobody wants — which is frequently the line somebody was looking for.

Whenever you write an inner join, ask what it excludes. The next lesson is the tool for keeping those rows.

And rows can multiply

The other half, from the counting lesson:

SELECT count(*) AS rows_after_join
FROM books b JOIN loans l ON l.book_id = b.id;

That is 180, not 40 — one row per loan, so a book borrowed eleven times appears eleven times.

books     40 rows
loans    180 rows
joined   180 rows   ← one per loan, books repeated

A join to the many side repeats the one side. That is what you want when you are listing loans with their book titles, and it is a disaster when you are summing something from books.

The rule from module 3, restated: aggregate before you join, or count distinct.

Joining more than two

SELECT m.name AS member, b.title, a.name AS author, l.due_on
FROM loans l
JOIN members m ON m.id = l.member_id
JOIN books   b ON b.id = l.book_id
JOIN authors a ON a.id = b.author_id
WHERE l.returned_on IS NULL
ORDER BY l.due_on
LIMIT 5;

Each JOIN adds a table and says how it connects to what is already there. They are applied left to right conceptually — though the planner may reorder them, which is module 7.

Start from the table you want one row per. Here that is loans: one row per outstanding loan, with the member, book and author attached. Starting from members instead would have given you one row per member per loan, which is the same rows in a less obvious order.

That choice — what is one row of my result? — is the thing to decide before writing the query.

`USING` and `NATURAL`, and why only one is safe

JOIN book_categories bc USING (book_id)      -- when both columns have the same name

USING (book_id) is shorthand for ON b.book_id = bc.book_id, and it merges the two into one output column. Tidy when the names match.

NATURAL JOIN book_categories                  -- do not

NATURAL JOIN joins on every column with a matching name, whatever they are. Add a created_at to both tables and the join silently changes meaning. It is a genuine footgun and there is no reason to use it.

The comma form you will meet in old code

SELECT b.title, a.name FROM books b, authors a WHERE a.id = b.author_id;

The pre-1992 spelling. It gives the same result and it has a failure mode: forget the WHERE and you get a cross join — every book against every author, 40 × 20 = 800 rows, with no error.

SELECT count(*) FROM books CROSS JOIN categories;    -- 40 × 10 = 400

Use explicit JOIN ... ON. It separates how the tables relate from which rows you want, and it cannot silently become a cross join.

CROSS JOIN written deliberately is occasionally useful — generating every combination of sizes and colours, or filling a date range — and when you mean it, say it.

Counting the join before trusting it

The habit that catches fan-out:

SELECT count(*) FROM books;                                       -- 40
SELECT count(*) FROM books b JOIN loans l ON l.book_id = b.id;    -- 180

If the count changes, know why. Growing means the join is to a many side — fine, if you expected it. Shrinking means rows were excluded — fine, if you expected that.

Doing this before writing the aggregate takes ten seconds and prevents the whole family of wrong-number bugs.

Check your work

What INNER JOIN keeps. Only rows where the condition holds on both sides.

Why use table aliases. Column names alone do not say which table they came from.

What ON is. A condition — usually a foreign key to a primary key.

What it means if you are joining on a name or a date. The schema is probably missing a relationship.

What an inner join silently does. Drops rows with no match — 40 books become 39.

The question to ask of every inner join. What does this exclude?

What a join to the many side does to the one side. Repeats it — 40 books become 180 rows.

What to decide before writing a multi-table join. What one row of the result is.

Why NATURAL JOIN is dangerous. It joins on every same-named column, so adding a column changes the query's meaning.

The failure mode of the comma form. A forgotten WHERE gives a silent cross join.

The ten-second habit. Count before and after the join, and know why the number changed.

Practice

Join books to authors and list twenty titles with their authors.
Add the aliases and then remove them. Note how the query reads.
Count books, then count DISTINCT b.id after joining to loans. Explain the difference of one.
Find which book it is.
Count rows before and after joining books to loans. Explain 40 becoming 180.
Join loans, members, books and authors to list every outstanding loan with all four pieces of information.
Rewrite that starting from members instead of loans. Compare the row counts and say which is one row per what.
Join books to book_categories with ON, then with USING. Note the difference in the output columns.
Write a NATURAL JOIN between two of these tables, then add a same-named column to both and run it again.
Write the comma form and deliberately forget the WHERE. Count the rows.
Write a deliberate CROSS JOIN and predict the row count before running it.
Join on a non-key condition — books published in the same year as another book — and decide whether the schema is missing something.

Official documentation

PostgreSQL — Joined tables — Every join type, with worked row-by-row examples.
PostgreSQL — SELECT reference — The formal FROM clause, including USING and NATURAL.
PostgreSQL — Table aliases — Including why a self join needs them.

Next: the joins that keep the rows with no match.