Join traps: double counting, and ON versus WHERE

There are two classic join bugs. Fan-out: joining a "many" table repeats the "one" side, so its numbers get summed several times. And a right-table filter in WHERE, which silently turns a LEFT JOIN into an INNER JOIN.

Why it matters

Both bugs return a result with no error, and the numbers look believable. The total revenue comes out as 1131 instead of 793; customers with no delivered orders quietly vanish from an "all customers" report. Interviewers test for exactly these.

The idea

Fan-out: after orders JOIN order_items, an order with 2 items appears on 2 rows, each carrying the full order amount. SUM(o.amount) now counts it twice. The fix is to sum at the right grain: aggregate the many side first (in a subquery or CTE), then join one row per order.

ON versus WHERE with a LEFT JOIN: a condition in ON decides which right rows match, and left rows are kept regardless. A condition in WHERE runs after the join, on the finished rows. For unmatched rows the right columns are NULL, so WHERE o.status = 'delivered' is unknown and the row is thrown away.

Fan-out is like counting a family's income once for each child in the family: join families to children and sum family income, and a family with three children is counted three times. ON versus WHERE is the difference between "invite every pupil, and note which ones brought a permission slip" and "invite only the pupils who brought one".

With a family you would notice the triple count. SQL can't: it has no idea which column belongs to which grain. Checking the row count after each join is the habit that catches it.

Syntax

-- keep all left rows, filter which right rows match:
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'delivered'

-- aggregate the many side first, then join:
JOIN (SELECT order_id, COUNT(*) AS items FROM order_items GROUP BY order_id) i
  ON i.order_id = o.order_id

How it works

  1. orders has 10 rows. Joined to order_items it has 14 rows, because 5 orders have 2 items each (and order 106 has none).
  2. Each of those rows carries its order's full amount, so summing amount adds 102, 103, 105, 109 and 110 twice: 1131.
  3. Fix: sum o.amount on orders alone, or pre-aggregate order_items to one row per order before joining.
  4. LEFT JOIN with AND o.status = 'delivered' in ON: all 6 customers are kept; only delivered orders are attached.
  5. The same condition in WHERE: customers with no delivered order have NULL status, so they are removed — 3 customers vanish.

Key points

Common mistakes

Not true: COUNT(*) after joining orders to their items still counts orders.
In fact: Each order now appears once for every item, so COUNT(*) counts items. Use COUNT(DISTINCT o.order_id) to count orders.

In the interview

The two join bugs I always check for are fan-out and misplaced filters. Fan-out happens when a one-to-many join repeats the parent row, so summing a parent column double-counts — I fix it by aggregating the child table to the parent's grain before joining. And with a LEFT JOIN, a filter on the right table belongs in the ON clause; in WHERE it discards the NULL-extended rows and silently becomes an inner join.

Part of Unit 3: Joins: combining tables in the free SQL course.