Why Your SQL Join Returns Too Many Rows (One-to-Many Fan-Out)
Joining orders to order items repeats each order once per product, so a SUM of the order total comes out almost three times too big. See the fan-out row by row, then add up the values from the right table.
Step by step
Too many rows
- A total that's too big. Adding a join can make a sales total almost 3 times too big. Nothing shows an error, so nobody notices. Here is why it happens and how to fix it.
- The true total. First, get the true answer. Sum order_total straight from the orders table, with no join. It returns 15,322. Keep that number to check against later.
SELECT SUM(order_total) AS sales FROM orders;
Add a join
- Add a join. Now join order_items to orders on order_id, as you might do to filter by product later. The SELECT stays the same: SUM(order_total) AS sales. It returns 40,311. It's the same question, but the answer is much bigger, and there is no error to warn you.
FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id; - Wrong total. This wrong number is what ends up in a report if nobody checks it.
Find the fan-out
- Look at one order. To see why, look at one order, 1004. Select its order_id and order_total from orders, and the product_id from order_items, with WHERE o.order_id = 1004. The query returns 3 rows for 1 order. Order 1004 has 3 products, so the join repeats the order once for each product.
SELECT o.order_id, o.order_total, i.product_id FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id WHERE o.order_id = 1004; - The same total, three times. The order_total of 452 appears on all 3 rows, and SUM adds it 3 times. This is called a fan-out.
The fix
- Sum the many side. The fix is to add up values from the many side. Each order_items row has a quantity and a unit_price, so sum i.quantity * i.unit_price. It returns 15,322 again. The true total is back, even with the join still in place.
SELECT SUM(i.quantity * i.unit_price) AS sales FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id;
Recap
- The fan-out rule. When 1 row matches many rows, a join repeats it and sums get too big. Check the row count after a join, and add up values from the many side.
Common mistakes
- Trusting a total because the query ran. A fan-out gives no error. Run the total without the join first, or compare row counts before and after the join, and check they match.
- Using SUM(DISTINCT order_total) to remove the repeats. It also drops different orders that happen to have the same total, so the sum comes out too small. Sum from the many side, or total the orders before you join.
- Assuming item lines always add up to the order total. Quantity times unit_price only matches order_total when the order has no shipping, tax or discounts. Check that both totals agree on your data before you swap one for the other.
- Counting orders with COUNT(*) after the join. COUNT(*) counts item rows, not orders, so order 1004 counts 3 times. Use COUNT(DISTINCT o.order_id) to count orders.
When a business analyst uses this
When checking a sales report before sign-off, compare its total with SELECT SUM(order_total) FROM orders. If the report joins order_items and shows a bigger number, a fan-out is the likely cause. Log it as a defect before the numbers reach the business.
Questions people ask
Why does my SQL join return more rows than expected?
When one row in the first table matches several rows in the second, the join repeats it once for each match. An order with 3 items becomes 3 rows.
What is a fan-out in SQL?
A fan-out happens when a one-to-many join repeats the 'one' row for every matching 'many' row. Any SUM of a column from the 'one' side then counts the same value several times.
How do I stop SUM from double counting after a join?
Sum a column from the many side, like quantity times unit_price from order_items. Or add up the one-side values in a separate query before you join.
