← All articles

Why Your LEFT JOIN Returns Too Many Rows

You wrote a query. It looked right. You joined orders to order_items to get the total spent per customer, and the numbers came back triple what they should be. Not wrong in a subtle way — wrong in a way that's obviously wrong, because your test customer with a $40 order is now showing $120.

You didn't misspell anything. You didn't use the wrong table. The join is doing exactly what a join does. The bug is in your mental model of what a join is.

The model everyone starts with, and why it's wrong

Most people learn joins as "combine two tables based on a matching column." That's true, but it hides the part that actually matters: a join doesn't produce one row per entity. It produces one row per match.

If an order has three line items, joining orders to order_items doesn't give you one row for the order with a list of three items attached. It gives you three rows, each one a full copy of the order's columns glued to a different item. The order didn't triplicate in your database — it triplicated in your result set, because that's what a relational join mechanically does: it's a cross-product filtered down to matching pairs, not a tree structure.

Once you see it this way, the bug stops being mysterious:

SELECT o.id, o.customer_id, SUM(oi.price) AS total
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.customer_id;

If orders also had a shipping_cost column and you summed that too, you'd hit the same trap in reverse — shipping_cost would get pulled into the sum once per line item, wildly overcounting a value that only existed once. The join doesn't know which columns are "per order" and which are "per item." It just duplicates the row and lets you deal with it.

The fix everyone reaches for first (and why it's wrong too)

The instinctive patch is SELECT DISTINCT. It's wrong for two different reasons, and both matter.

Reason one: it doesn't fix the aggregate. SUM(oi.price) runs before DISTINCT removes duplicate output rows — DISTINCT operates on the final result set, but the sum has already been computed across every duplicated row by the time it gets there. Your total is still wrong.

Reason two: even when it "fixes" something, it's fixing the symptom by hiding data, not addressing the cause. If you're joining orders to items without aggregating, and you throw DISTINCT * at it because you're getting weird dupes, you'll silently collapse two genuinely different rows that happen to look identical on the columns you selected — a real bug that produces plausible-looking output, which is the worst kind of bug.

DISTINCT is a band-aid for a symptom. The actual fix is to stop the row explosion before it reaches your aggregate, not clean up after it.

Three real fixes, in order of how often you'll reach for them

1. Pre-aggregate before you join.

This is the cleanest fix for the "total per parent" case, because it never lets the row explosion happen in the first place:

SELECT o.id, o.customer_id, COALESCE(item_totals.total, 0) AS total
FROM orders o
LEFT JOIN (
  SELECT order_id, SUM(price) AS total
  FROM order_items
  GROUP BY order_id
) item_totals ON item_totals.order_id = o.id;

Now the subquery collapses order_items down to one row per order before it ever touches orders. There's nothing to duplicate. This is usually also the fastest version, because your database engine can aggregate the smaller table first instead of carrying every joined row through the whole query.

2. Use a window function when you need both the detail rows and the total.

Sometimes you actually want every line item and the order total on each row — a receipt, for instance. A window function does this without collapsing anything:

SELECT
  o.id,
  oi.product_name,
  oi.price,
  SUM(oi.price) OVER (PARTITION BY o.id) AS order_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id;

PARTITION BY o.id tells the database "compute this sum per order, but don't collapse the rows." You get the row-level detail and the aggregate side by side, correctly, in one pass.

3. Conditional aggregation for one-to-many-to-many joins.

If you're joining through two one-to-many relationships at once — say, orders to items and separately orders to shipments — a naive join multiplies both, and you get a combinatorial mess where the item count and shipment count both look inflated and neither total is trustworthy. Pre-aggregate each side independently (fix #1, applied twice) before joining them together. Never join two "many" sides directly to a common parent in the same query if you're also aggregating — do it in two subqueries and join those.

The habit that actually prevents this

Before you write SUM, COUNT, or AVG on a joined query, ask one question: is the table I'm joining to a "many" relative to the table I'm aggregating from? If yes, the row count on your target table has already multiplied by the time your aggregate function sees it. Either pre-collapse that side with a subquery, or use OVER (PARTITION BY ...) if you need the detail rows to survive.

Count your joins before you trust your sums. Every duplicated-total bug in production SQL traces back to skipping that one check.

Want to practice this exact pattern against a real database instead of reading about it? SQL Quest has a lesson that puts you in front of this exact bug with a live query editor — no signup, runs in your browser.