Join fan-out: how to check a JOIN before you trust the SUM

Finance at Meridian, a fictional practice marketplace, asks how much commission is left after paying for shipping. Most orders ship in one parcel, some in two. Join orders to their parcels and look at two orders:

Order J000030 ships in two parcels, so its commission is there twice. These three rows say €15.11 is left after shipping. The two orders really leave €4.68. On the whole table:

SELECT SUM(o.commission_eur) - SUM(p.shipping_cost_eur) AS left_after_shipping
FROM meridian.cart_orders AS o
JOIN meridian.parcels AS p ON p.order_id = o.order_id;
-- result: 9,494.38

€9,494 is wrong. The right answer is €6,807. The join added only 3.7% more rows, and the result is 39% too high. Nothing errored.

What join fan-out is

A join returns one row for every matching pair. When a key matches several rows on the other side, like an order with two parcels, the row from the first table is repeated, and every number on it is repeated too. That is join fan-out.

Why your JOIN returns duplicate rows shows the loud version, where a total more than doubles and someone notices. This page is about the quiet one: a few percent more rows and a result that still looks plausible.

Why 3.7% more rows made it 39% wrong

Three things stack up:

1. The repeated orders are not average. Orders that need two parcels are bigger: €7.24 of commission on average, against €4.62 for one-parcel orders. So 3.7% more rows add 5.7% more commission: €49,622 instead of €46,935.
2. The shipping side is right. Each parcel is in the join exactly once, so shipping adds up to €40,128, as it should.
3. The answer is a difference. The extra €2,687 of commission all lands in what is left after shipping, a number far smaller than either total. €2,687 on top of €6,807 is 39%.

Margins, profit, net revenue: anything "after costs" behaves like this. A fan-out that hides in a total grows in a difference.

Three queries that catch it

Run them on any join before you trust a SUM or AVG over it.

1. Count the rows before and after the join.

SELECT
  (SELECT COUNT(*) FROM meridian.cart_orders) AS orders,
  (SELECT COUNT(*)
   FROM meridian.cart_orders AS o
   JOIN meridian.parcels AS p ON p.order_id = o.order_id) AS rows_after_join;
-- result: 9,944 · 10,315

More rows after the join means some rows are repeated. Fewer means an inner join dropped rows without a match. Either way, the totals changed.

2. Find the keys that repeat.

SELECT COUNT(*) AS orders_with_several_parcels
FROM (
  SELECT order_id
  FROM meridian.parcels
  GROUP BY order_id
  HAVING COUNT(*) > 1
) AS repeated;
-- result: 371

This tells you the grain of the parcels table: one row per parcel, not per order. 371 orders will be counted twice.

3. Compare a total with its own table.

SELECT SUM(commission_eur) AS commission
FROM meridian.cart_orders;
-- result: 46,935.13

After the join the same column adds up to €49,622. When every order should appear exactly once after the join, a column from the first table must keep its total. Here it grew, so some orders were repeated. A total can also change for the opposite reason: an inner join drops orders with no match. Check both separately: count repeated keys, and count orders that went missing.

The fix: one row per key, then join

Add up the parcels per order first, then join:

WITH shipping AS (
  SELECT order_id, SUM(shipping_cost_eur) AS shipping_eur
  FROM meridian.parcels
  GROUP BY order_id
)
SELECT
  COUNT(*) AS orders,
  SUM(o.commission_eur) - SUM(s.shipping_eur) AS left_after_shipping
FROM meridian.cart_orders AS o
JOIN shipping AS s ON s.order_id = o.order_id;
-- result: 9,944 · 6,807.24

The row count is back to 9,944, one row per order, and the COUNT(*) next to the answer shows it. Don't fix fan-out with SUM(DISTINCT o.commission_eur): two different orders can have the same commission, and DISTINCT drops one of them.

Try it on a colleague's number

Try it · 30 seconds · from Atterna’s practice

I

Ines · finance

How much of our commission is left after we pay for shipping? I joined orders to parcels and got €9,494.

	orders table	after the join
rows	9,944	10,315
commission	€46,935	€49,622
shipping	—	€40,128

The join added 3.7% more rows. How far off is €9,494?

Preparing the practice…

About 4%, like the rowsAbout 6%, like the commissionAbout 39%

Send the grain with the number

"€6,807 of commission left after shipping, over 9,944 orders, 371 of them in two parcels." The row count in that sentence is the check, and anyone who reads it can repeat it.

Practise defending the number

Fan-out is a favourite in junior analyst SQL screens because the query runs and the answer looks right. The case round after the SQL screen asks the harder part: your query runs, can you defend the number? Try the interview question: one question, one tap, the answer, then case 1. It is free.

New to joins? Start, Atterna's SQL course from zero, introduces INNER and LEFT JOIN on day 8 and inflated totals on day 9. All 10 days are free with an account. Start day 1.

FAQ

What is join fan-out in SQL?

Join fan-out is when a join returns more rows than the table you started from, because a key matches several rows in the other table. Each repeated row carries its numbers with it, so SUM and COUNT(*) over the first table's columns come out too high. An AVG becomes weighted by how many rows each order matched, so it can rise, fall or stay the same, and an unchanged average doesn't rule fan-out out. On the Meridian practice data, 9,944 orders joined to their parcels give 10,315 rows.

How do I check whether a join duplicates rows?

Count rows before and after the join. Then count the keys that appear more than once in the joined table with GROUP BY key HAVING COUNT(*) > 1. Finally compare the total of one column from the first table before and after the join. If any of these differ, the join fanned out.

Why is my SUM only slightly too high after a JOIN?

Because only some keys match more than one row on the other side. When few keys repeat, a total grows by a few percent and still looks plausible. The error gets much bigger when you subtract two totals: on Meridian, 3.7% more rows made the commission left after shipping 39% too high.

How do I fix join fan-out?

Aggregate the many side to one row per key in a subquery or CTE, then join. Or skip the join if you don't need the other table's columns. Avoid SUM(DISTINCT …) and SELECT DISTINCT as fixes: they remove real values that happen to be equal.