Chapter 3 · 8 min
Join fan-out and duplicate control
Joining a one-to-many relationship multiplies rows. One order joined to its five line items produces five rows, and summing a column from the orders side now counts the order five times. This fan-out is the most common way a join produces a number that is wrong by a factor the query never mentions.
-- Revenue is fine here: each line item is counted once.
SELECT sum(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE o.status = 'paid';
The same join becomes wrong when the aggregate is on the other side:
-- Each order's id is repeated once per line item, so this overcounts.
SELECT count(*) AS orders
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'paid';
count(DISTINCT o.order_id) repairs the count. It is not a general repair: sum(o.total) still
adds the order total once per line item, and distinct does not help because the repeated totals are
legitimate duplicates from the join’s point of view.
Aggregate before you join
When a metric belongs to one side, compute it there and join the result:
WITH per_order AS (
SELECT order_id, sum(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
)
SELECT o.order_id, o.status, per_order.order_total
FROM orders o
JOIN per_order ON per_order.order_id = o.order_id
WHERE o.status = 'paid';
Now the aggregate has one row per order, the join does not fan out, and the result means what it says. This pattern is worth naming in an interview: pre-aggregate the many side, then join on the key.
DISTINCT is not a deduplication strategy
SELECT DISTINCT removes identical result rows. It does not decide which row is the right one, and
it cannot repair an aggregate that already counted duplicates. If two rows differ in one column, the
distinct result still has two rows. Use DISTINCT when the question genuinely asks for unique
combinations, and use grouping or a pre-aggregated join when the question asks for a metric.
EXISTS when you only need a yes or no
If the question is “which orders have at least one item”, the answer does not need the item rows.
EXISTS stops at the first match and avoids fan-out entirely:
SELECT o.order_id
FROM orders o
WHERE EXISTS (
SELECT 1 FROM order_items oi WHERE oi.order_id = o.order_id
);
Choosing EXISTS over a join is both a correctness decision and a clarity decision. It says the
question is about presence, not about the matched rows.