A reporting query summed order totals correctly until a second table was joined in; now the total is roughly double, though no data changed. Explain what happened in terms of row multiplicity, and describe how you would fix it without simply adding a duplicate-removal step.
answer
- join = multiset multiply, one row per match
- sum inflated, avg reweighted, min/max survive
- count(*) vs count(distinct parent id) diagnostic
- semi-join for existence, pre-aggregate for measures
- distinct after the join deletes real equal rows
basics
~20 sThe join is a bag operation: each parent row is emitted once per matching child row, so its total is added several times. Fix it by aggregating the child side first and joining that, or by using an existence check when you only need filtering - not by removing duplicates afterwards.
solid answer
~50 sJoining is multiset multiplication. If one order has three line items, the join emits that order's row three times, and summing the order total then counts it three times - classic fan-out. Nothing about the data changed; the multiplicity of the aggregate's input did. Three correct fixes, depending on intent. If you only need to filter on the child's existence, use an existence check (a semi-join), which returns at most one row per parent and therefore preserves multiplicity. If you need child values aggregated as well, pre-aggregate the child in a derived table keyed by the parent id and join that - one row per parent, no fan-out. If you need child detail rows in the output too, compute the parent measure in a separate block rather than in the same one. What I avoid is adding a duplicate-elimination step or a distinct-qualified aggregate. Those hide the fan-out and quietly delete legitimately equal values - two orders of exactly 100.00 collapse into one.
code
sql · 8 linesSELECT o.order_id,
o.total,
i.item_count
FROM orders o
JOIN (SELECT order_id, COUNT(*) AS item_count
FROM order_items
GROUP BY order_id) i
ON i.order_id = o.order_id;go deeper
Be able to say that a join repeats the parent row once per matching child row, so summing it counts the same amount several times.
Add the fix: aggregate the child side first and join that, or use an existence check when the child table is only a filter.
Show diagnosis (row counts before/after, count vs count-distinct, min/max unaffected) and explain why distinct-based patches are incorrect, not merely slow.
Discuss preventing the whole class of bug: declared keys so uniqueness is provable, reporting views that pre-aggregate, and tests that assert multiplicity rather than membership.
## What fan-out is A join produces one output row for every pair of rows satisfying the predicate. If each parent row matches k child rows, the parent's columns are repeated k times in the join output. Because a SQL result is a bag, those repeats are real rows and every operator above the join sees them. An aggregate placed above the join therefore consumes the parent measure k times. The measure was never wrong; the multiplicity of its input was multiplied. The effect is not uniform, which is what makes it treacherous: the inflation factor is the child count per parent, so orders with one line item are correct, orders with five are five times too large, and the total is off by an unpredictable amount that depends on data distribution. It also gets worse as data grows, so a query that looked right on a small test set breaks in production. Chaining two one-to-many joins multiplies the factors: three items and two shipments give six rows per order. ## Which aggregates are damaged Counting rows counts join output rows, not entities. Summing a parent measure over-counts. Averaging is worse than summing, because it is silently reweighted - parents with many children dominate the average and no single correction factor exists. Minimum and maximum survive, since repeating a value does not change the extreme; that is a useful diagnostic, because if min and max look right while sum and count look inflated, you are almost certainly looking at fan-out. ## Diagnosis Compare the row count of the query before and after adding the join: if it grew, the join is not one-to-one. Compare a plain row count with a count of distinct parent ids in the same result - if they differ, each parent appears more than once. Check the join predicate against declared keys: a join that matches on a parent's primary key or on a unique constraint of the joined table cannot fan out, and this is exactly the reasoning the optimizer itself uses to prove uniqueness is preserved. ## Fixes that preserve multiplicity **Semi-join (existence check).** When the child table is only a filter - 'orders that have at least one returned item' - the right operator is a semi-join: it returns each parent row at most once regardless of how many children match. That is the whole reason semi-join exists as a distinct operator. An inner join cannot substitute for it without a duplicate-elimination step glued on, and that step both costs more and changes the answer when genuinely equal parent rows exist. The mirror case, 'parents with no matching child', is an anti-join. **Pre-aggregation.** When you need child measures, aggregate the child table by the parent key in a derived table first, then join that result. Because the derived table has exactly one row per parent key, the join is one-to-one and the parent measure is counted once. This is often the faster plan too, because the aggregation happens on the narrow child rows. **Separate aggregates.** When you need several unrelated child aggregates - items and shipments - compute each in its own pre-aggregated derived table, or use scalar subqueries. Joining both children in one block is the classic 'cartesian between siblings' bug where the two child counts multiply. ## Why 'just deduplicate' is the wrong instinct Adding a duplicate-elimination step after the join removes repeated *rows*, but two distinct orders whose output columns happen to be identical - same date, same amount, no id selected - are indistinguishable, and one is silently deleted. A distinct-qualified aggregate has the same defect: it collapses genuinely equal values, so summing distinct amounts drops the second order of exactly 100.00. Both also pay a sort or hash on every execution to repair a problem that could be removed structurally. Reach for them only when you have proved the duplicates are truly the same entity - for example because the entity's key is in the output.
- Why does a semi-join preserve the left input's row multiplicity when an inner join does not?A semi-join is defined to return a left row at most once, as soon as one matching right row is found; it stops probing after the first match. An inner join is defined to return every matching pair, so a left row appears once per match. That is precisely why an existence-style filter is duplicate-safe and rewriting it as an inner join is not, unless uniqueness on the right side is provable.
- When is joining a one-to-many child genuinely safe without pre-aggregation?When the join predicate covers a key of the joined table, so at most one row can match - for example joining a child to its parent by the parent's primary key, or joining on a column set carrying a unique constraint. The optimizer uses the same reasoning, but only if the constraint is declared; merely believed uniqueness gives you neither correctness guarantees nor better plans.
Photocopying an invoice once per attached receipt, then adding up the pile: the invoice amount is real, you just have four copies of it.
saying these in an interview costs you the question
- Adding a duplicate-elimination step after the join and calling the sum fixed
- Using a distinct-qualified sum, which silently drops legitimately equal amounts
- Claiming the join 'corrupted the data' rather than changed row multiplicity
- Assuming a skewed average can be repaired by dividing by the child count
- Joining two different one-to-many children in one block and not expecting their counts to multiply