Join Fan-Out and Row Multiplication
Why a join can return more rows than either table and how interviewers test it.
When a Join Returns Too Many Rows
One of the most revealing interview questions sounds innocent: "can a join return more rows than the larger table?" The answer is yes, and the phenomenon is called fan-out or row multiplication.
Candidates who say "a join just combines tables" miss it. Candidates who can predict the exact row count get hired. This lesson builds that prediction skill.
The Cause: One-to-Many Matches
Fan-out happens when one row on the left matches many rows on the right. Each match produces a separate output row.
With customers and orders, Ada (one customer) has two orders. The join emits one row per order, so Ada is duplicated. The customer fields repeat; only the order fields differ.
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;
-- Ada appears twice (she has 2 orders)
-- name | amount
-- Ada | 50
-- Ada | 20
-- Bob | 99All lessons in this course
- How INNER JOIN Matches Rows
- ON vs WHERE in Joins
- Join Fan-Out and Row Multiplication
- Joining Three or More Tables