A Join to the Many Side Multiplies Rows and Quietly Doubles Your SUM
When you join a table to another table where several rows match, each original row is repeated once per match, so ten orders with three items each produce thirty rows. Any SUM or COUNT taken after that join counts the repeated copies, which is how a total ends up twice as big as it should be. The usual patches are DISTINCT or a grouping, but both make the engine process the inflated rows first; aggregating before the join, or using EXISTS when you only need a yes or no, avoids creating them at all.
Questions this Concept answers
- Why can joining an orders table to its order items make `sum(orders.total)` come out too large?
Why a JOIN returns duplicate rows (fan-out)
When you join a table to another table that has many matching rows, each original row gets repeated once per match. If you then add up a column from the first table, the repeats get counted more than…