An INNER JOIN matches rows from two tables wherever the join condition holds, and returns only those matched rows. The condition is written in ON, and it is the entire definition of the join: no condition, no rows. Everything else — how many rows come back, whether a book appears twice, whether a row disappears entirely — follows from which columns you compare.
How does an INNER JOIN match rows?
Three tables hold the facts of a loan: the loan itself, the book that went out, and the member who took it. An inner join pairs a loan with its book and its member:
SELECT l.loan_id, b.title, m.name AS member
FROM loans l
JOIN books b ON l.book_id = b.book_id
JOIN members m ON l.member_id = m.member_id
ORDER BY l.loan_id
LIMIT 6;
loan_id title member
-------- ------------------------- -----------
1 The Salt Ledger Priya Shah
2 Winter Light Daniel Okoye
3 Cartography for Beginners Hana Sato
4 Letters from the Delta Louis Marchand
5 Signals and Noise Amara Nwosu
6 Harbour of Small Boats Priya Shah
Read the ON clauses as sentences. "Keep the loan whose book_id equals a book's book_id." "Keep the loan whose member_id equals a member's member_id." Every matching pair becomes one output row, and the columns of both tables sit side by side in the result. The aliases l, b and m are not decoration — book_id exists in both loans and books, and only the alias says which one you mean.
What happens to rows that do not match?
They disappear. That is the defining behaviour of an inner join, and it is easy to miss because the rows that vanish are exactly the ones you were not thinking about. Count before and after the join:
SELECT (SELECT COUNT(*) FROM loans) AS loans_rows,
(SELECT COUNT(*) FROM loans l JOIN books b ON l.book_id = b.book_id
JOIN members m ON l.member_id = m.member_id) AS joined_rows;
loans_rows joined_rows
----------- -----------
14 14
Fourteen in, fourteen out — every loan in this dataset has a matching book and a matching member, so the inner join happens to be lossless. That is a property of the data, not of the join type. The moment a loan references a book that has been deleted from the catalogue, the two counts diverge and the loan silently vanishes from the report. Lesson 23 is about the join type that keeps it visible.
Why can one row appear many times after a join?
A join multiplies, it does not zip. If two loans point at the same book, that book contributes two output rows. Count them per book:
SELECT b.book_id, b.title, COUNT(*) AS loan_rows
FROM books b
JOIN loans l ON l.book_id = b.book_id
GROUP BY b.book_id, b.title
ORDER BY loan_rows DESC, b.book_id
LIMIT 4;
book_id title loan_rows
------- ------------------------- ---------
1 The Salt Ledger 2
7 Signals and Noise 2
2 Harbour of Small Boats 1
3 Cartography for Beginners 1
The Salt Ledger and Signals and Noise were each borrowed twice, so they occupy two rows each. The total row count after the join reflects that:
SELECT COUNT(*) AS rows_after_join FROM books b JOIN loans l ON l.book_id = b.book_id;
rows_after_join
---------------
14
Thirteen books joined to fourteen loans gives fourteen rows, not thirteen and not one hundred and eighty-two. The join's output grain is the finer of the two tables, and knowing that grain is what tells you whether a number you are about to compute is a per-book figure or a per-loan figure. It is also why counting after a join needs care:
SELECT COUNT(*) AS books_with_loans, COUNT(DISTINCT b.book_id) AS distinct_books
FROM books b JOIN loans l ON l.book_id = b.book_id;
books_with_loans distinct_books
---------------- --------------
14 12
COUNT says fourteen because it counts joined rows; COUNT DISTINCT says twelve because only twelve distinct books were borrowed. If the report asks "how many books have been borrowed", the second column is the honest one — and if it asks "how many loans were placed", the first is. Always decide which grain the question lives at before you choose the counting function.
Does the ON clause have to compare keys with =?
No. Equality on the key is the common case, but the condition is an ordinary boolean expression, so ranges work too — a query that matches every loan made after a member joined is a join with ON l.loaned_on > m.joined_on. That kind of condition is called a non-equi join, and it is the right tool for questions about intervals rather than relationships. Two cautions apply to every join, equi or not. The order of the ON clauses does not change the answer — you may write books JOIN authors or authors JOIN books and get the same rows — because the optimiser rearranges the work anyway; only the left-to-right readability of your query depends on it. And a condition that is too loose will still return rows: join the two tables on books.genre = branches.city and you get a valid result, just a meaningless one. There is no error for a wrong join, which is why checking the row count is not optional.
Quick reference
- INNER JOIN returns only rows that match on both sides; unmatched rows on either side are dropped.
- The ON clause is the whole join — an incorrect condition silently returns the wrong rows, not an error.
- Joins multiply: the result grain is the finer of the two tables.
- COUNT after a join counts joined rows; COUNT DISTINCT counts the entities you actually meant.
- Alias every table as soon as two of them share a column name.
Join drill: write an inner join that lists every loan with the book title, the member name and the branch that holds the book — then run a variant with the ON clause on the wrong column and explain, in one sentence, why it still returns rows instead of an error.