Join traps and row explosions

The fan-out: two children, multiplied rows

Recall lesson 1: a join is a product, and the row count multiplies. A fan-out is that multiplication striking where you didn’t expect it. Take one parent (orders) with two one-to-many children: order_items (the line items) and payments (installments). Join all three in one query:

-- WRONG: revenue is doubled (or worse)
SELECT SUM(o.total) AS revenue,
       SUM(oi.quantity) AS units
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN payments    p  ON p.order_id  = o.id;

If an order has 3 items and 2 payments, the join produces 3 × 2 = 6 rows for that single order — every item paired with every payment, because both children join on order_id independently. Now SUM(o.total) adds that order’s total 6 times, and SUM(oi.quantity) counts each item 2 times (once per payment). The order’s amount didn’t change; the number of rows carrying it did. The two children fanned out against each other — a Cartesian product within each order’s group.

The fix: pre-aggregate each child before joining

The rule is blunt: never join two independent one-to-many children to a parent and then aggregate. Collapse each child to one row per parent first — in a subquery or CTE — then join those pre-aggregated results, which are now one-to-one with the parent and cannot fan out:

-- RIGHT: each child aggregated to one row per order, then joined
SELECT o.id,
       o.total,
       items.units,
       pays.paid
FROM orders o
LEFT JOIN (
  SELECT order_id, SUM(quantity) AS units
  FROM order_items GROUP BY order_id
) items ON items.order_id = o.id
LEFT JOIN (
  SELECT order_id, SUM(amount) AS paid
  FROM payments GROUP BY order_id
) pays ON pays.order_id = o.id;

Each subquery returns at most one row per order_id, so neither join multiplies. SUM(o.total) over this is now correct. The pattern generalizes to any number of children: aggregate each in its own CTE/subquery, then join the one-row-per-parent summaries. (LATERAL from the last lesson is another way to compute these per-parent aggregates inline.)

For a quick count you can sometimes patch a fan-out with COUNT(DISTINCT oi.id) instead of COUNT(*) — distinct counting ignores the duplicated rows. But SUM(DISTINCT o.total) is a trap: two different orders can legitimately have the same total, and DISTINCT would collapse them. COUNT(DISTINCT) rescues counts; only pre-aggregation reliably rescues sums.

The other traps: missing conditions, duplicate keys, join order

The fan-out is the subtle one, but three blunter traps share the unit. When you review a query that joins several tables, ask: does each join key constrain the relationship, is it unique on at least one side, and are there multiple independent children being joined to the same parent?

• Missing join condition → the accidental cross join from lesson 1. A forgotten ON, or an ON that doesn’t actually relate the tables, yields |A| × |B| rows — an instant explosion, not a quiet doubling.
• Non-unique join keys → even an “innocent” join duplicates rows if the key isn’t unique on the side you assumed was unique. Joining orders to users on email instead of id will fan out if two users share an email. Always join on a key that is unique on at least one side, or know exactly why it isn’t.
• Join order and the planner → you write joins left to right, but the planner reorders them freely to minimize cost; the result is identical regardless of written order (for inner joins). So don’t hand-tune join order for correctness or speed — fix the predicates and statistics and let the planner choose. (Outer joins constrain reordering; the databases execution-plans chapter covers when.)