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. You add a join, and suddenly your sales total is almost three times bigger. Nothing crashed, so nobody notices. Let's see why it happens, and how to fix it.
- The true total. First, the true answer. The sum of the order total, from orders. Fifteen thousand, three hundred and twenty two. Remember that number.
SELECT SUM(order_total) AS sales FROM orders;
Add a join
- Add a join. Now someone joins order items, maybe to filter by product later. Same sum, but from orders joined to order items on the order ID. Forty thousand, three hundred and eleven. Same question, a much bigger answer. No error, just a wrong number.
FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id; - Wrong total. This is the number that ends up in a report if nobody checks it.
Find the fan-out
- Look at one order. Let's look at a single order, ten oh four. Show its ID, its total, and the product ID from order items. Three rows for one order. It has three 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. And here's the problem. The same total, four hundred and fifty two, three times. Sum adds all three. That's called a fan-out.
The fix
- Sum the many side. The fix is to add up values from the many side. Each item row has a quantity and a unit price. Quantity times price, summed, gives the sales. Fifteen thousand, three hundred and twenty two. The true total is back, even with the join.
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. So, when one row matches many, a join repeats it, and sums get too big. Check the row count after a join, and add up values from the many side.
