The real reporting rarely stops at two tables, so the chaining of the joins across many tables is the normal case. A loan belongs to a book, a book belongs to an author and a branch, and a branch has the staff. Each additional table is one more JOIN clause and one more place where the row count can change. That is why chaining the joins is a skill of the two habits. You name every table with an alias, and you check the row count after every step.
The previous lesson in this module covered "What Is a Self Join and When Do You Need One?", and this lesson is the last one in the Joining Tables module.
How do you chain the joins?
The order of the clauses in the FROM list does not change the result, because the optimiser decides the physical order. It does change how readable the query is.
The convention is to follow the path of the data. You start at the table that you are reporting on, and then you follow the foreign keys outwards. Here that means the loans, then the book, then the author, and 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 Riverside BranchThere are four joins and four aliases, and the result reads from the left to the right as a sentence about a loan. Notice what each join contributes. The m brings the name of the member, the b brings the title, the a brings the writer, and the br brings the holding branch.
The chain works because the keys line up in the sequence. The loans.book_id goes to the books.book_id, then the books.author_id goes to the authors.author_id, and then the books.branch_id goes to the branches.branch_id. Each table joins to exactly one other table in the chain, and that 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 14There are fourteen rows and there are fourteen distinct loan identifiers. The chain is one-to-one along the way, so nothing multiplied.
If those two numbers ever differ, then one of the joins is fanning out, and the fix is to find which one of them it is. The classic culprit is joining the two tables that both hang off the same parent. Joining the books to the authors and the books to the loans gives one row per loan, but joining the loans to the authors through the books and also to the members through the 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 the books to the 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 1The unborrowed book sorts to the top because it has the zero loans. That is exactly the report that a stock-take wants.
There are two rules that make the chained outer joins safe. You keep every outer join pointing in the direction of the entity that you want preserved, because a left join that is later joined to again will drag the NULLs across the whole result. And you count with the COUNT(join_key) and never with the COUNT(*), once a NULL can appear.
How do you aggregate across a chain?
The chain exists to enrich the rows, and then the aggregation collapses them. A branch summary joins the branches to the books to the loans, and it counts the distinct books separately from the 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 3Riverside holds five books and it has lent six of them, because one book went out twice.
The two counts answer the different questions. How much stock and how much activity. Counting the distinct books alongside a loan count is what stops the two of them from being confused.
This is the shape of nearly every operational dashboard. There is a chain that gathers the context, and then there is one GROUP BY that summarises it.
Quick reference
- You alias every table in a chain, and you order the FROM list along the path of the foreign keys.
- You check the COUNT(*) against the COUNT(DISTINCT key) after every join, to spot a fan-out.
- The one-to-one chains preserve the row count, and the one-to-many steps multiply it.
- An outer join inside a chain changes the grain of every later step.
- You count with the COUNT(join_key) whenever the NULLs can appear, and you use the COUNT DISTINCT for the entity counts.
Chain drill: you build a single query that returns, for every author, the number of the books, the number of the loans on those books and the total pages of their books. Then you state which one of the three numbers needs the COUNT DISTINCT to be correct.