Lessons · SQL · when a join multiplies rows

When a join multiplies rows

Joining one order to its three items gives three rows, each carrying the order's amount. Summing that column afterwards counts the order three times.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Revenue that doubles after a join, a total that disagrees with finance: fan-out is the usual cause, and the query runs without a single error.

How to think about it

Before summing after a join, ask what one row means now. Aggregate the many side first in a CTE, then join one row per key; and compare the row count before and after the join.

Worked example

SELECT o.id, o.amount, r.stars FROM orders o JOIN reviews r ON r.customer_id = o.customer_id;

One row per order-review pair: an order with three reviews now appears three times.

WITH per_customer AS (SELECT customer_id, COUNT(*) AS reviews FROM reviews GROUP BY customer_id) SELECT o.id, o.amount, p.reviews FROM orders o JOIN per_customer p ON p.customer_id = o.customer_id;

Aggregate the many side first: one row per customer, so each order stays one row.

Your turn

Check the join kept one row per order.

SELECT COUNT(*) FROM orders o JOIN per_customer p ON p.customer_id = o.customer_id; -- compare with SELECT COUNT(*) FROM ;

The trap

A key that repeats on both sides multiplies: three rows each side make nine. Deduplicate the lookup side before joining.

Practise when a join multiplies rows on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.