The join that doubles revenue

A join combines rows from two tables that share a key, such as order_id. When one row on one side matches many rows on the other, the join repeats it. Analysts call this fan-out. The query runs, the table looks tidy, and the total is too high.

AI tools add joins freely, sometimes to tables the answer doesn't need. The check takes ten seconds: count rows and distinct keys after each join. If they differ, something is repeating.

A worked example

An AI wrote this for "paid revenue by country":

SELECT c.country, SUM(o.amount_usd) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY c.country;

The query never uses order_items, so that join only does harm. Here is how one analyst found and fixed it:

Check after the joins:  COUNT(*) = 3,412   COUNT(DISTINCT order_id) = 1,208
Different, so a join repeats orders. order_items is never used.
Fix: delete the order_items join. Now both counts are 1,208 and the
total matches finance. (If you need item columns, total the items per
order first, then join that result.)

Try it