0Pricing
SQL Interview Prep · Lesson

Join Fan-Out and Row Multiplication

Why a join can return more rows than either table and how interviewers test it.

Join Fan-Out and Row Multiplication is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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  | 99

Counting the Output Rows

The output row count equals the sum of matches per left row, not the number of customers.

  • Ada -> 2 orders -> 2 rows
  • Bob -> 1 order -> 1 row
  • Cleo -> 0 orders -> 0 rows (dropped by INNER JOIN)

Total = 3 rows, even though customers has 3 rows too. Change Ada to 10 orders and the result jumps to 11 rows.

Many-to-Many Explodes

Fan-out compounds when both sides have multiple matches for the same key. If key K appears 3 times on the left and 4 times on the right, the join produces 3 x 4 = 12 rows for that key.

This is how a seemingly small join balloons into millions of rows. Interviewers love giving you duplicate keys on both sides to see if you spot the multiplication.

-- left has 3 rows with tag 'A', right has 4 rows with tag 'A'
SELECT l.id, r.id
FROM left_t l
JOIN right_t r ON r.tag = l.tag;
-- tag 'A' alone yields 3 * 4 = 12 output rows

The Aggregation Trap

Here is the bug interviewers plant most often. You join orders to order_items to get item details, then SUM the order amount. Because each order fans out into multiple item rows, the order amount is counted once per item.

The SUM is now wildly inflated. The query looks correct and even runs, which is what makes it dangerous.

-- BUG: order.amount duplicated across items
SELECT SUM(o.amount) AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id;
-- a 3-item order counts o.amount 3 times

Seeing the Inflation

Suppose one order has amount 100 and three line items. The join produces three rows, each carrying amount 100. SUM(o.amount) returns 300, not 100.

The fix is to aggregate at the right grain: sum the items, or sum distinct orders separately. Never SUM a parent value across a fanned-out child join.

o.id | o.amount | i.id
7    | 100      | 71
7    | 100      | 72
7    | 100      | 73
-- SUM(o.amount) = 300  (WRONG, should be 100)

Fix 1: Aggregate the Child First

The cleanest fix is to pre-aggregate the many side in a subquery or CTE so each parent matches exactly one summarized row. No fan-out, no inflation.

Here we collapse items to one row per order before joining, so the parent amount is never duplicated.

SELECT o.id, o.amount, i.item_count
FROM orders o
JOIN (
  SELECT order_id, COUNT(*) AS item_count
  FROM order_items
  GROUP BY order_id
) i ON i.order_id = o.id;

Fix 2: COUNT(DISTINCT) and Conditional Sums

If you must aggregate after a fan-out join, count or sum the right grain. Use COUNT(DISTINCT o.id) to count orders rather than item rows.

Note: SUM(DISTINCT o.amount) is NOT a safe fix, because two different orders can legitimately share the same amount and would be merged. Pre-aggregation is more reliable.

SELECT COUNT(DISTINCT o.id)   AS num_orders,
       COUNT(i.id)            AS num_items
FROM orders o
JOIN order_items i ON i.order_id = o.id;

Detecting Fan-Out Before It Bites

A quick diagnostic interviewers like: check whether the join key is unique on the side you expect to be the "one". If the count of distinct keys is less than the row count, that side has duplicates and will fan out.

-- if this returns rows, order_id is NOT unique in order_items
SELECT order_id, COUNT(*) AS n
FROM order_items
GROUP BY order_id
HAVING COUNT(*) > 1;

Verifying Your Grain With a Count

Before trusting any aggregate over a joined result, sanity-check the row count. A fast trick: compare the joined count to the count of the table you expect to be the grain.

If COUNT(*) over the join is larger than COUNT(*) of orders, the join has fanned out and any per-order aggregate is at risk. This one-line check has saved many interview answers.

-- joined rows should equal order count if no fan-out
SELECT COUNT(*) AS joined_rows
FROM orders o
JOIN order_items i ON i.order_id = o.id;

SELECT COUNT(*) AS order_rows FROM orders;
-- joined_rows > order_rows  =>  fan-out present

Fan-Out Is Not Always a Bug

Sometimes you want one row per child. Listing every line item with its order header is correct fan-out. The skill is knowing your target grain: how many rows should one entity produce?

State the grain before writing the query. "I want one row per order item" versus "one row per order" decides whether fan-out is feature or bug.

Quick Check

Predict the output of a one-to-many join.

Recap: Fan-Out and Row Multiplication

What to remember:

  • A join emits one row per matching pair, so one-to-many matches duplicate the "one" side.
  • Many-to-many keys multiply: 3 x 4 = 12 rows for that key.
  • Aggregating a parent value across a fanned-out join inflates sums and counts.
  • Fix by pre-aggregating the child, or by counting/summing at the correct grain (e.g. COUNT(DISTINCT)).
  • Always state the intended grain first; fan-out is only a bug when it violates it.

Frequently asked questions

Is the “Join Fan-Out and Row Multiplication” lesson free?

Yes — the full text of “Join Fan-Out and Row Multiplication” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Join Fan-Out and Row Multiplication”?

Why a join can return more rows than either table and how interviewers test it. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Join Fan-Out and Row Multiplication” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. How INNER JOIN Matches Rows
  2. ON vs WHERE in Joins
  3. Join Fan-Out and Row Multiplication
  4. Joining Three or More Tables
← Back to SQL Interview Prep