A join returns one row for every pair of matching rows, not one row per row of the first table. Count the matching pairs and you know the answer before you run the query.
This lesson belongs to SQL reasoning in a fictional database, part of database development. If joins themselves are new, read joining related tables first.
How do you count the rows a join will return?
Take each row of the first table and count how many rows in the second table match it. Add those counts. That total is the number of rows in an inner join.
Here is an original school library. The Borrower table has three rows, and the Loan table has four.
| BorrowerID | Name |
|---|---|
| B1 | Aina |
| B2 | Dev |
| B3 | Wei |
| LoanID | BorrowerID | Book |
|---|---|---|
| L1 | B1 | Atlas |
| L2 | B1 | Poems |
| L3 | B1 | Comics |
| L4 | B2 | Atlas |
Worked example: a correct join with a surprising count
The query is: SELECT Name, Book FROM Borrower INNER JOIN Loan ON Borrower.BorrowerID = Loan.BorrowerID.
Count the pairs. Aina matches three loans, Dev matches one, and Wei matches none, so the total is 3 + 1 + 0 = 4 rows. The result has more rows than Borrower, which has only 3.
Nothing has gone wrong. BorrowerID is the primary key in Borrower, but it is a foreign key in Loan, where it can repeat. One borrower to many loans means one name can appear many times.
Wei does not appear at all. An inner join drops rows with no partner, so the row count can be larger or smaller than either table.
The mistake: a join with no matching condition
A student writes SELECT Name, Book FROM Borrower, Loan and forgets the condition. Each of the 3 borrowers is paired with each of the 4 loans, so the result has 3 × 4 = 12 rows.
Aina appears beside “Atlas” taken by Dev. That row is meaningless, yet it looks like data.
| Query | Rows | Why |
|---|---|---|
| INNER JOIN with ON BorrowerID | 4 | One row per matching pair |
| No condition at all | 12 | Every borrower paired with every loan |
| LEFT JOIN with ON BorrowerID | 5 | The 4 pairs plus Wei with NULL in Book |
The habit to build is this: before you read the output, say aloud which column links the two tables. If you cannot name it, the join is wrong.
When are extra rows really a problem?
Extra rows are a problem only when the question wants one row per borrower. “List the names of borrowers who have a loan” needs SELECT DISTINCT Name, which returns Aina and Dev once each, so 2 rows.
“How many loans does each borrower have?” needs GROUP BY. That is a different tool, and the lesson on distinguishing row filtering from group filtering shows where it fits.
Check yourself
Table Team has 3 rows: T1, T2 and T3. Table Player has 6 rows linked by TeamID. T1 has two players, T2 has four players, and T3 has none.
(a) How many rows does an inner join on TeamID return?
(b) How many rows does the same query return if the ON condition is left out?
Answer
(a) Count the pairs: 2 + 4 + 0 = 6 rows. T3 has no player, so it is dropped.
(b) Without a condition, every team pairs with every player: 3 × 6 = 18 rows.
Notice that the inner join gives 6, the same as the Player table, because each player belongs to exactly one team.
What to study next
Extra rows also change totals, so join first and aggregate second with care. Continue with testing an aggregate query with empty groups and missing values, then try the cluster practice set.
You can run your own tables through the SQL reasoning sandbox. If you want a teacher to check your predictions with you, see online one-to-one Computer Science tuition.