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_idHow it works
- orders has 10 rows. Joined to order_items it has 14 rows, because 5 orders have 2 items each (and order 106 has none).
- Each of those rows carries its order's full amount, so summing amount adds 102, 103, 105, 109 and 110 twice: 1131.
- Fix: sum o.amount on orders alone, or pre-aggregate order_items to one row per order before joining.
- LEFT JOIN with
AND o.status = 'delivered'in ON: all 6 customers are kept; only delivered orders are attached. - The same condition in WHERE: customers with no delivered order have NULL status, so they are removed — 3 customers vanish.
Key points
- After every join, ask: what does one row mean now? Did the row count grow?
- Never sum a "one"-side column after joining a "many" table. Sum first, or pre-aggregate the many side.
- Using DISTINCT to hide fan-out hides the bug, not fixes it — sums can still be wrong.
- With a LEFT JOIN, conditions on the right table go in ON; conditions on the left table go in WHERE.
- With an INNER JOIN, ON and WHERE give the same result.
Common mistakes
- Writing
SUM(order.amount)after joining order_items: each order's amount is then added once for every item it has. COUNT(*)after a fan-out join, counting items instead of orders. UseCOUNT(DISTINCT o.order_id).- Writing
LEFT JOIN … WHERE right.col = xin an "all customers" report: the WHERE throws away the customers that had no match.
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.