Real reporting rarely stops at two tables, so chain joins across many tables are the normal case. A loan belongs to a book, a book belongs to an author and a branch, a branch has staff. Each additional table is one more JOIN clause and one more place for the row count to change, so chaining joins is a skill of two habits: name every table with an alias, and check the row count after every step.
How do you chain joins across many tables?
The clause order in the FROM list does not change the result — the optimiser decides the physical order — but it does change how readable the query is. The convention is to follow the path of the data: start at the table you are reporting on, then follow the foreign keys outwards. Here that means loans, then the book, then the author, then the branch:
SELECT l.loan_id, m.name AS member, b.title, a.name AS author, br.name AS branch
FROM loans l
JOIN members m ON l.member_id = m.member_id
JOIN books b ON l.book_id = b.book_id
JOIN authors a ON b.author_id = a.author_id
JOIN branches br ON b.branch_id = br.branch_id
ORDER BY l.loan_id
LIMIT 6;
loan_id member title author branch
-------- -------------- ------------------------- ----------------- ----------------
1 Priya Shah The Salt Ledger Ursula Vane Riverside Branch
2 Daniel Okoye Winter Light Iris Lindqvist Riverside Branch
3 Hana Sato Cartography for Beginners Tomas Guerrero Riverside Branch
4 Louis Marchand Letters from the Delta Marguerite Aubert Old Town Branch
5 Amara Nwosu Signals and Noise Nnamdi Obi Hillcrest Branch
6 Priya Shah Harbour of Small Boats Ursula Vane Hillcrest Branch
Four joins, four aliases, and the result reads left to right as a sentence about a loan. Notice what each join contributes: m brings the member's name, b the title, a the writer, br the holding branch. The chain works because the keys line up in sequence — loans.book_id to books.book_id, then books.author_id to authors.author_id, then books.branch_id to branches.branch_id. Each table joins to exactly one other table in the chain, which is what keeps the geometry of the query simple.
What happens to the row count as the chain grows?
This is the question to ask after every join. The four-table chain should return one row per loan, and it does:
SELECT COUNT(*) AS joined_rows, COUNT(DISTINCT l.loan_id) AS distinct_loans
FROM loans l
JOIN members m ON l.member_id = m.member_id
JOIN books b ON l.book_id = b.book_id
JOIN authors a ON b.author_id = a.author_id
JOIN branches br ON b.branch_id = br.branch_id;
joined_rows distinct_loans
----------- --------------
14 14
Fourteen rows and fourteen distinct loan identifiers — the chain is one-to-one along the way, so nothing multiplied. If those two numbers ever differ, one of the joins is fanning out, and the fix is to find which one. The classic culprit is joining two tables that both hang off the same parent: joining books to authors and books to loans gives one row per loan, but joining loans to authors through books and also to members through loans can multiply if a member appears twice in the same table.
What happens when the chain includes a LEFT JOIN?
An outer join inside a chain changes the grain of everything after it. A left join from books to loans keeps the unborrowed book, and the chain still reports it:
SELECT b.book_id, b.title, a.name AS author, COUNT(l.loan_id) AS loans
FROM books b
LEFT JOIN authors a ON b.author_id = a.author_id
LEFT JOIN loans l ON l.book_id = b.book_id
GROUP BY b.book_id, b.title, a.name
ORDER BY loans, b.book_id
LIMIT 5;
book_id title author loans
------- ------------------------- ----------------- -----
13 A History of Fermentation Marguerite Aubert 0
2 Harbour of Small Boats Ursula Vane 1
3 Cartography for Beginners Tomas Guerrero 1
4 The Copper Wire Tomas Guerrero 1
5 Winter Light Iris Lindqvist 1
The unborrowed book sorts to the top because it has zero loans — which is exactly the report a stock-take wants. Two rules make chained outer joins safe. Keep every outer join pointing in the direction of the entity you want preserved, because a left join that is later joined to again will drag the NULLs across the whole result. And count with COUNT(join_key), never COUNT(*), once a NULL can appear.
How do you aggregate across a chain?
The chain exists to enrich the rows; the aggregation then collapses them. A branch summary joins branches to books to loans, and counts distinct books separately from loans:
SELECT br.name AS branch, COUNT(DISTINCT b.book_id) AS books, COUNT(l.loan_id) AS loans
FROM branches br
JOIN books b ON b.branch_id = br.branch_id
LEFT JOIN loans l ON l.book_id = b.book_id
GROUP BY br.name
ORDER BY loans DESC, br.name;
branch books loans
---------------- ----- -----
Riverside Branch 5 6
Hillcrest Branch 5 5
Old Town Branch 3 3
Riverside holds five books and has lent six of them, because one book went out twice. The two counts answer different questions — how much stock, how much activity — and counting distinct books alongside a loan count is what stops the two from being confused. This is the shape of nearly every operational dashboard: a chain that gathers the context, then one GROUP BY that summarises it.
Quick reference
- Alias every table in a chain, and order the FROM list along the path of the foreign keys.
- Check COUNT(*) against COUNT(DISTINCT key) after every join to spot a fan-out.
- One-to-one chains preserve the row count; one-to-many steps multiply it.
- An outer join inside a chain changes the grain of every later step.
- Count with COUNT(join_key) whenever NULLs can appear, and COUNT DISTINCT for entity counts.
Chain drill: build a single query that returns, for every author, the number of books, the number of loans on those books and the total pages of their books — then state which of the three numbers needs COUNT DISTINCT to be correct.