An INNER JOIN matches the rows from the two tables wherever the join condition holds, and it returns only those matched rows. The condition is written in ON, and it is the entire definition of the join. There is no condition and there are no rows. Everything else, which is how many rows come back, whether a book appears twice, and whether a row disappears entirely, follows from which columns you compare.
The previous lesson in this module covered "Why Do Relational Databases Split Data Across Tables?", and the next lesson in this module is about "What Do LEFT and RIGHT Joins Preserve?".
How does an INNER JOIN match the rows?
Three of the tables hold the facts of a loan. There is the loan itself, there is the book that went out, and there is the member who took it. An inner join pairs a loan with its book and with 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 ShahYou read the ON clauses as the 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 of the tables sit side by side in the result.
The aliases l, b and m are not decoration. The book_id exists in both the loans and the books, and only the alias says which one of them you mean.
What happens to the 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 that you were not thinking about. You count before the join and after it.
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 14There are fourteen in and there are 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 and not a property of the join type.
The moment that 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 and it does not zip. If two loans point at the same book, then that book contributes two output rows. You 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 1The Salt Ledger and the 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
---------------
14Thirteen books joined to fourteen loans gives you fourteen rows. It is not thirteen, and it is not one hundred and eighty-two. The output grain of the join is the finer of the two tables, and knowing that grain is what tells you whether a number that you are about to compute is a per-book figure or a per-loan figure.
It is also why the counting after a join needs the 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 12The COUNT says fourteen because it counts the joined rows. The COUNT DISTINCT says twelve because only twelve distinct books were borrowed. If the report asks how many books have been borrowed, then the second column is the honest one. If it asks how many loans were placed, then the first one is. You always decide which grain the question lives at before you choose the counting function.
Does the ON clause have to compare the keys with an equals?
It does not. The equality on the key is the common case, but the condition is an ordinary boolean expression, so the ranges work too. A query that matches every loan made after a member joined is a join with the condition ON l.loaned_on > m.joined_on. That kind of condition is called a non-equi join, and it is the right tool for the questions about the intervals rather than the relationships.
There are two cautions that apply to every join, equi or not. The order of the ON clauses does not change the answer. You may write the books JOIN authors or the authors JOIN books and you 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 the rows. You 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
- The INNER JOIN returns only the rows that match on both of the sides, and the unmatched rows on either side are dropped.
- The ON clause is the whole join. An incorrect condition silently returns the wrong rows and not an error.
- The joins multiply. The result grain is the finer of the two tables.
- The COUNT after a join counts the joined rows, while the COUNT DISTINCT counts the entities that you actually meant.
- You alias every table as soon as two of them share a column name.
Join drill: you write an inner join that lists every loan with the title of the book, the name of the member and the branch that holds the book. Then you run a variant with the ON clause on the wrong column, and you explain in one sentence why it still returns the rows instead of an error.