How a SQL JOIN Can Quietly Multiply Your Revenue

The dashboard that "grew" 135% overnight

A finance analyst adds one join to a revenue query — not to change what's being measured, but to pull in a product category for a new filter. The query still runs, still returns rows for every customer, still looks completely normal. Total revenue on the dashboard jumps from $1,160 to $2,730 the same day. No refunds were processed. No new orders were entered. The join to order_items, added purely to read product_name, quietly turned every order with more than one line item into multiple copies of itself — and every copy carried its full order total into the SUM().

The exact mechanism

This is not a rare edge case — it's the direct, mechanical consequence of aggregating after a one-to-many join:

-- Added ONLY to filter by product category — but now inflates revenue:
SELECT SUM(o.total_amount) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE oi.product_name LIKE '%Mouse%';
-- Every order with 3 line items now contributes its total 3 times.

The fix preserves the filter while preventing the multiplication — check for the matching product in a subquery instead of joining to it directly:

SELECT SUM(o.total_amount) AS revenue
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM order_items oi
  WHERE oi.order_id = o.id AND oi.product_name LIKE '%Mouse%'
);

EXISTS checks a condition without ever multiplying orders rows, because it never joins the two tables into one combined row set — it just asks yes/no per order. For the full breakdown of every fix pattern, see Why Is My SQL SUM() Wrong After a JOIN?.

Build your own — the JOIN Fan-Out Visualizer

Reading about fan-out is one thing — building a broken revenue number with your own hands is another. We built a free tool that lets you set up your own orders, give some of them multiple line items, and watch the exact inflation happen live, then run the real SQL yourself to confirm it.

Why this survives code review

A reviewer scanning a pull request checks whether the JOIN condition is correct and whether the WHERE clause filters the right rows — both of which can be completely correct here. What a review rarely checks is whether an aggregate function downstream of that JOIN is still seeing one row per original entity. The bug isn't in the JOIN's logic; it's in the distance between the JOIN and the aggregate, and that distance is exactly what's easy to lose track of in a longer query.

Live example: same bug, real SQL

Five customers, six orders, ten line items — the same dataset used throughout this series, this time viewed as a monthly revenue report. Run the buggy version and January's revenue reads far higher than it should; run the correct version and it drops back to the real number.

How to prevent it going forward

• Never join a many-side table just to filter — use EXISTS/NOT EXISTS instead, which checks a condition without multiplying rows.
• Pre-aggregate before joining whenever you do need values from the many-side table, so the join stays one-to-one by the time it reaches an aggregate.
• Add a row-count sanity check to recurring revenue queries — compare COUNT(*) against the known number of orders for the period; a mismatch is an instant fan-out signal.
• Re-verify any revenue query after someone adds a join to it, even a join that looks unrelated to the SUM() — the addition of any one-to-many join anywhere in the query can retroactively break a previously-correct aggregate.

Challenge: fix the monthly revenue report

Same dataset, reframed as a monthly and regional revenue report. Load the starter, write your query, and check your answer instantly.