P06 · SQL & Querying
SQL fan-out — when a join multiplies your rows
How a one-to-many join changes the grain in SQL — and the trap where summing after the join counts a value once per match and inflates the total.
Start with the basics
Every query has a grain — the thing one row represents. One row per order, one row per shipment. Joins are where the grain quietly changes.
Join one order to its many shipments and the order's row is repeated once per shipment. That is fan-out: the one side is multiplied by the many side.
Read on its own it's harmless. The damage comes when you sum a value after the fan-out — you count the order's amount once per shipment instead of once.
One that works
| id | amount |
|---|---|
| 1 | 100 |
| 2 | 50 |
SELECT SUM(amount) AS total
FROM orders;| total |
|---|
| 150 |
Summed at its own grain — one row per order — the total is the honest 150.
Joining one row to many multiplies it, so any total you compute after the join is inflated by the number of matches.
When to use it
- Think about grain whenever you join a one-to-many relationship (orders to line items, users to events).
- Aggregate to the right grain before joining, or use an existence check instead of a join.
- Sanity-check totals against the pre-join numbers when a join is involved.
That multiplication is where a total quietly inflates. Sum a value after joining one row to many, and you count it once per match instead of once. Here is the day that bites.
See it happen
The revenue that doubled — summing after a join
Order 1 has two shipments. You join orders to shipments and sum the amount. Watch the join multiply order 1's amount and inflate revenue.
| id | amount |
|---|---|
| 1 | 100 |
| 2 | 50 |
| order_id |
|---|
| 1 |
| 1 |
| 2 |
sum after the join — counts per match
SELECT SUM(o.amount) AS total
FROM orders o
JOIN shipments s ON s.order_id = o.id;| total |
|---|
| 250 |
check existence — counts once
SELECT SUM(o.amount) AS total
FROM orders o
WHERE EXISTS (
SELECT 1 FROM shipments s WHERE s.order_id = o.id
);| total |
|---|
| 150 |
$250 is a believable revenue number — the timebomb that passes review, ships, and misstates revenue by the fan-out ratio.
Walk the full example in the unit →The rest of the family
More ways fan-out inflates a total
Comparing the row count to the distinct-key count, or counts before and after the join, reveals whether the join multiplied rows.
Joining through a bridge table explodes the grain in two directions at once.
A missing join condition pairs every row with every row — a Cartesian blow-up.
Decide 'one row per X' up front; the grain is a decision, not something to discover after the query runs.
Self-check
How to spot it in your own SQL
- A total is a clean multiple of the real number (double, triple).
- You summed a value from the 'one' side after joining the 'many' side.
- Row counts jumped after adding a join.
- A join with a missing condition returned an enormous result.
What people miss
Fan-out is the timebomb of reporting: a one-to-many join multiplies a value, and the inflated total is a believable number that passes review, ships, and misstates the metric by the match ratio.
Go deeper
Ask Colearn
Instant answers, grounded in the same verified material the diagnostic grades against.
Why did my revenue total double after a join?
Joining one order to its many shipments repeats the order's amount once per shipment, so summing afterward counts it multiple times.
Aggregate to the right grain before the join, or use an existence check instead of joining.
from the unit →How do I detect fan-out in a query?
Compare the row count to the count of distinct keys, or the counts before and after the join — a jump means the join multiplied rows.
If they differ, the grain changed and any post-join total is suspect.
from the unit →Why did my join return a huge number of rows?
A missing join condition pairs every row on one side with every row on the other — a Cartesian product.
Add the join condition, or check for an accidental comma join.
from the unit →How do I avoid fan-out when I need a total across a join?
Decide the grain first, then either pre-aggregate the many side to the one side's grain, or replace the join with an existence check when you only need a filter.
State 'one row per X' before writing so the grain is a choice, not an accident.
from the unit →That’s the free taste