September 5, 2026

Why your query returns the wrong number of rows

Two causes account for almost all of it — a join that multiplied rows, and a WHERE that deleted them. Both are easy to see once you know where to look.

You run a query, glance at the row count, and it's wrong — too many, or fewer than you know are in the table. Nearly every case comes down to one of two things: a join multiplied your rows, or a WHERE clause quietly deleted some. Here's how to spot each.

Cause 1: a join multiplied your rows

A JOIN produces one row per matching pair. If the join key isn't unique on the other side, every row gets duplicated once per match.

Loading editor…

Seven customers. Now join in their orders:

Loading editor…

Eleven rows — one per order, not per customer. Customer 1 has three orders, so "Acme Corp" now appears in the result three times. This is called fan-out, and it's harmless until you aggregate something from the customer side, at which point it's counted once per order.

The same question, three different answers

"How many customers per country?" — three queries that all look reasonable:

Loading editor…

US = 8? That's counting orders placed by US customers, after fan-out. COUNT(DISTINCT ...) counts the thing you actually meant:

Loading editor…

US = 3 now — but this only counts customers who have at least one order. If the question was really "how many customers per country," the orders table shouldn't be in the query at all:

Loading editor…

US = 4. Three different numbers — 8, 3, 4 — from three queries that each look like they answer the question. The fix isn't a clever function, it's deciding what grain the result is supposed to be at and not joining in tables that change it.

Fixing fan-out

• Only need columns from one table? Don't join the other one.
• Need an aggregate from the "many" side? Aggregate it in a subquery first, then join the one-row-per-key result.
• Counting distinct things? COUNT(DISTINCT id) — but know that it also excludes anything with zero matches.

Cause 2: a WHERE clause deleted the rows

A LEFT JOIN keeps every row from the left table, matched or not:

Loading editor…

Two customers have no orders at all. A LEFT JOIN keeps them, with NULL in every orders column. Now add what looks like a harmless filter:

Loading editor…

Four. Not seven. The two customers with no orders have NULL for o.status, and NULL = 'paid' is not true — so WHERE drops them. So does the customer whose only order is pending. The LEFT JOIN did its job and then WHERE undid it: a condition on an outer-joined table's column turns a LEFT JOIN back into an INNER JOIN, plus an extra filter.

Fixing it

Move the condition into the ON clause, where it filters what gets joined instead of what survives:

Loading editor…

All seven customers, and the ones with no paid orders correctly show 0 instead of vanishing. If the condition genuinely has to be in WHERE, the other option is to let NULL through explicitly: WHERE o.status = 'paid' OR o.status IS NULL.

DISTINCT is a symptom-hider, not a fix

Adding DISTINCT to a fanned-out query collapses the duplicate rows and makes the count look right — but any SUM, AVG, or COUNT in that same query was computed before the de-duplication, over the multiplied rows, so it's still wrong. DISTINCT on a result you didn't expect to have duplicates is a sign the join grain is off; fix that instead.

How to diagnose it in 30 seconds

1. SELECT COUNT(*) on the base table. Note the number.
2. Add each join one at a time, re-running COUNT(*). The join that changes the count more than you expected is your fan-out.
3. Check whether the join key is unique on the side being joined in — if it isn't, fan-out is possible.
4. Look at every WHERE condition: is it on a column from a LEFT/RIGHT joined table? If so, it's silently dropping the unmatched rows.