Joins That Multiply: Fan-Out and Double Counting
Find out why a joined total can be far too big, spot the fingerprint of a fan-out and fix it by aggregating before you join.
In this lesson you will
- Explain a fan-out
- Spot one from row counts
- Fix it by aggregating first
- Check totals before trusting them
About 28 minutes
The total that was too big
Dev adds a shipping column to the orders table and writes a quick query for the total shipping charged on shipped orders. The answer is far too large. He checks the data, the filter and the arithmetic, and each is fine. The cause is the join. Joining orders to their lines before adding up a column that belongs to the order makes each order’s value repeat once for every line. It is called a fan-out, and it is among the most common causes of wrong reports in SQL. The query runs without an error and returns a believable number, which is exactly why it is dangerous.
Why it happens
A join to a ‘many’ table makes one row for each match. An order with three lines becomes three rows after the join. Any value stored on the order, such as the shipping charge, is copied onto all three. A SUM over that column then counts the charge three times. Totals of line-level values, such as quantity times price, are safe, because each line appears once. The trouble starts when you mix levels, adding up order-level values after a join to the lines.
Five signs to look for
- A total that is larger than the source or the finance system says.
- A row count after a join that is higher than the table you started from.
- DISTINCT added to ‘fix’ duplicates. It may hide the problem or remove real rows.
- An average that looks odd, because big orders are weighted by their number of lines.
- Totals that change when you add an unrelated join.
Four queries that show it
For this lesson the orders table gets a made-up shipping charge of 4 per order, added in the setup. Query 1 adds up the shipping on shipped orders without any join. Query 2 does the same after joining to the lines. Query 3 compares row counts and distinct orders. Query 4 shows the repair: add up the lines per order first, in a CTE, so that the join to orders is one row to one row.
-- 1
SELECT ROUND(SUM(shipping), 2) AS shipping_correct FROM orders WHERE status = 'shipped'
-- 2
SELECT ROUND(SUM(o.shipping), 2) AS shipping_after_join FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id WHERE o.status = 'shipped'
-- 3
SELECT (SELECT COUNT(*) FROM orders) AS orders_rows,
(SELECT COUNT(*) FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id) AS rows_after_join,
(SELECT COUNT(DISTINCT o.id) FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id) AS distinct_orders
-- 4
WITH items AS (SELECT oi.order_id, SUM(oi.qty * p.price) AS item_total
FROM order_items AS oi JOIN products AS p ON p.id = oi.product_id GROUP BY oi.order_id)
SELECT ROUND(SUM(i.item_total), 2) AS items, ROUND(SUM(o.shipping), 2) AS shipping,
ROUND(SUM(i.item_total + o.shipping), 2) AS grand_total
FROM orders AS o JOIN items AS i ON i.order_id = o.id WHERE o.status = 'shipped'
-- 1
shipping_correct
28.00
-- 2
shipping_after_join
60.00
-- 3
orders_rows | rows_after_join | distinct_orders
10 | 19 | 10
-- 4
items | shipping | grand_total
257.00 | 28.00 | 285.00
Query 1 gives 28.00, which is 7 shipped orders at 4 each. Query 2 gives 60.00, because the 15 shipped order lines each carried a copy of the charge. The first is correct, and the second is more than twice as big. Query 3 shows the cause in rows: the orders table has 10 rows, but the join produced 19, yet it still contains only 10 different orders. A joined result with many more rows than it has distinct keys, 19 rows for 10 orders here, is the fingerprint of a fan-out. Query 4 repairs it. The CTE first collapses the lines to one row per order, so the join is one to one. It reports items of 257.00, shipping of 28.00 and a grand total of 285.00.
Ways to fix it
- Aggregate before joining. Reduce the many side to one row per key in a CTE, then join. This is the reliable fix.
- Sum at the right level. Add up order-level columns from the orders table alone, as in query 1.
- Use distinct counts for counting. COUNT of a distinct key counts orders correctly after a join, but never use DISTINCT to repair a SUM.
- Reconcile. Compare each total with a trusted figure computed another way.
A habit that catches nearly all of it
Before you trust a query with joins, write down the grain of the result, what one row means. Then check the row count at each step. If you join a one-to-many table, expect the row count to rise, and plan for it. If a value belongs to the ‘one’ side, do not add it up after the join. Five minutes of this habit prevents reports that mislead for months.
Adding DISTINCT to make a duplicated total look right. It may remove rows that really are the same value, such as two orders that happen to have the same shipping, and the total is wrong in a new way.
The shipping charge and the data are made up, in sample money. The effect of a fan-out is the same in every database. The queries ran in SQLite.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What is a fan-out?
- A kind of index
- A way to split a table
- A faster join
- A join that repeats a value once for each matching row on the many side
The shipping charge was repeated on every order line.
Why was the joined shipping total 60 instead of 28?
- Fifteen shipped lines each carried the order charge
- The data was wrong
- The filter was missing
- SUM ignores NULLs
15 times 4 is 60.
What is the best fix?
- Add DISTINCT to the SUM
- Remove the join
- Aggregate the many side to one row per key before joining
- Round the answer
A one-to-one join cannot multiply rows.
What is the fingerprint of a fan-out?
- Fewer rows
- A NULL total
- An error message
- More rows after the join than in the first table, with the same distinct keys
19 rows but only 10 different orders.
Which total is safe after joining to order lines?
- Shipping stored on the order
- Quantity times price at line level
- The order date
- The customer id
Each line appears once, so line-level values are not repeated.
Summary
- A join to a many side repeats one-side values.
- Row counts reveal a fan-out.
- Aggregate to one row per key before joining.
- Never repair a SUM with DISTINCT.
Questions
Does COUNT DISTINCT fix a fan-out?
It fixes counting of keys, but not sums of repeated values. Aggregate before joining for sums.
Can a LEFT JOIN cause it too?
Yes. Any join to a many side repeats the one-side rows, whatever the join type.
Is the shipping charge real?
No. It is a made-up value added to show the effect.
