You join two tables that each have a reasonable number of rows, and the result comes back with way more rows than either table, or noticeably fewer than you expected. Nothing in the query looks broken. No error message appears. The row count is just wrong, and it's easy to assume the database is doing something strange.
It isn't. A SQL JOIN always follows the same simple logic: match rows based on your condition, and return one output row for every matching pair. Once you see your join through that lens, wrong row counts almost always trace back to one of a small number of causes.
Quick Answer: Why is my row count wrong?
Too many rows almost always means duplicate keys on one side of the join, called a fan-out, where one row matches several rows on the other table. Too few rows usually means a WHERE clause is accidentally filtering out unmatched rows from a LEFT JOIN, because NULL values fail most comparisons. Check for duplicate keys with a GROUP BY and HAVING COUNT(*) > 1, and move any filter conditions on the joined table into the ON clause instead of WHERE.
On this page
- The One Rule That Explains Almost Every Case
- Too Many Rows: The Fan-Out Problem
- How to Find the Duplicate Keys
- Fixing a Fan-Out Join
- Too Few Rows: The WHERE Clause Trap
- The Accidental Cross Join
- Why DISTINCT Isn't a Real Fix
- A Quick Diagnostic Checklist
- Key Takeaways
- Frequently Asked Questions (FAQs)
The One Rule That Explains Almost Every Case
A JOIN produces one output row for every matching pair between the two tables, not one row per record from either original table. If a row on one side matches three rows on the other side, you get three output rows, not one. That single fact explains nearly every "wrong row count" situation you'll run into.
Too Many Rows: The Fan-Out Problem
This is by far the most common version of the problem. If your customers table has one row per customer, but your orders table has three rows for one particular customer, joining the two on customer ID produces three rows for that customer, one for each order, each showing the same customer details repeated.
Whether this is a bug or expected behavior depends entirely on what you actually wanted. If you wanted a list of every order with its customer's name attached, three rows is exactly correct. If you wanted a summary with one row per customer, three rows is a fan-out that will quietly inflate any sum or count you run against it afterward.
How to Find the Duplicate Keys
Before touching the join itself, confirm which table actually has the duplicate keys causing the fan-out.
SELECT customer_id, COUNT(*) AS row_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY row_count DESC;Any customer_id showing up more than once here confirms that table will produce multiple matching rows for that key. Run the same check against both tables in your join if you're not sure which side is the source of the extra rows.
Fixing a Fan-Out Join
Once you know which table has multiple matching rows, there are a few different ways to handle it, depending on what you actually need the result to look like.
| What you need | Approach |
|---|---|
| One row per customer with total order value | Aggregate first with SUM and GROUP BY before joining, or aggregate after joining |
| One row per customer with only their most recent order | Use a window function like ROW_NUMBER, partitioned by customer, and filter for rank 1 |
| Every order listed individually with customer details attached | Keep the join as-is; the multiple rows are correct, not a bug |
| A count of distinct customers, unaffected by how many orders each has | Use COUNT(DISTINCT customer_id) rather than COUNT(*) |
SELECT
c.customer_id,
c.customer_name,
SUM(o.order_total) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;Aggregating with GROUP BY, as shown above, collapses the fan-out back down to one row per customer while still correctly summing the total across all of that customer's orders. If you're rusty on how GROUP BY and window functions differ here, our guide on SQL window functions explained covers exactly this distinction with a clear mental model.
Too Few Rows: The WHERE Clause Trap
The opposite problem, a LEFT JOIN returning fewer rows than the left table has, almost always traces back to a filter condition in the WHERE clause that accidentally excludes unmatched rows. This happens because a LEFT JOIN fills in NULL for any column from the right table when there's no match, and most comparison conditions in a WHERE clause fail automatically against NULL.
-- This silently turns your LEFT JOIN into an INNER JOIN
SELECT c.customer_name, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01';
-- Fixed: move the condition into the JOIN itself
SELECT c.customer_name, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
AND o.order_date >= '2026-01-01';In the first query, any customer with no matching order at all gets filtered out entirely, because their order_date is NULL and NULL never satisfies a comparison like >=. Moving the date condition into the ON clause, as shown in the second query, preserves customers with no orders while still filtering the orders that do exist.
The Accidental Cross Join
If your row count is wildly higher than a fan-out would explain, sometimes in the thousands or millions when neither table is that large, check whether you accidentally joined without a proper condition at all. Listing two tables in the FROM clause without a matching ON or WHERE condition produces a cross join, pairing every row in the first table with every row in the second.
-- Accidental cross join: no join condition
SELECT * FROM customers, orders;
-- What was probably intended
SELECT * FROM customers c JOIN orders o ON c.customer_id = o.customer_id;If each table has 100 rows, the cross join above returns 10,000 rows, every possible pairing, which is rarely what anyone actually wants.
Why DISTINCT Isn't a Real Fix
Adding DISTINCT to a query with a fan-out problem often makes the wrong row count disappear, which is exactly why it's tempting. It's usually the wrong fix. DISTINCT works by comparing entire rows and removing exact duplicates, so it can accidentally strip out rows that are legitimately different but happen to look similar after you've selected only a few columns.
A Quick Diagnostic Checklist
- Row count too high? Run a GROUP BY with HAVING COUNT(*) > 1 on each table's join key to find where the duplicates live.
- Row count dramatically too high, more than a fan-out explains? Check for a missing or incorrect join condition (an accidental cross join).
- Row count too low on a LEFT JOIN? Check whether a WHERE clause condition on the right-hand table is silently filtering out unmatched rows.
- Considering DISTINCT? Pause and confirm you understand exactly which rows it would remove and why, first.
Key Takeaways
- A JOIN returns one row per matching pair, not one row per original record. Extra rows almost always mean duplicate keys on one side.
- Use GROUP BY with HAVING COUNT(*) > 1 to find exactly which keys are duplicated before trying to fix anything.
- Fewer rows than expected on a LEFT JOIN usually means a WHERE clause condition is filtering out NULLs from unmatched rows. Move that condition into the ON clause instead.
- An unexpectedly massive row count can mean an accidental cross join from a missing or malformed join condition.
- DISTINCT hides the symptom without explaining the cause, and can silently remove legitimately different rows. Diagnose first, deduplicate second.
Frequently Asked Questions (FAQs)
Why does my SQL JOIN return duplicate rows?
This almost always means one of your tables has more than one row matching the same join key, commonly called a fan-out. For example, if a customer has three orders, joining customers to orders produces three rows for that customer, one per order, which is often correct but can look like an unwanted duplicate if you were expecting one row per customer.
Should I use DISTINCT to fix duplicate rows from a JOIN?
DISTINCT can hide the symptom but it does not fix the underlying cause, and it can silently remove rows that are genuinely different, not just duplicated. It is better to understand why the join is producing extra rows first, using an aggregate, a window function, or a more precise join condition, and use DISTINCT only as a last resort once you understand exactly what it is removing.
Why does a LEFT JOIN sometimes return fewer rows than my main table has?
This usually means a filter condition in the WHERE clause is accidentally excluding rows that had no match, because a NULL value from the unmatched side fails most comparison conditions. Move the filter into the JOIN's ON clause instead of WHERE, or explicitly account for NULLs in your WHERE condition.
What is a fan-out join and why does it matter?
A fan-out happens when a row on one side of a join matches multiple rows on the other side, multiplying the row count. It matters because if you then sum or count another column without being aware of the fan-out, you will get an inflated, incorrect total, since values get repeated once for every matching row.
How do I check for duplicate keys before joining two tables?
Run a GROUP BY on the join key with a COUNT, and filter for counts greater than 1 using HAVING. Any key that appears more than once in a table you expected to have unique keys is a signal that your join will fan out on that table.
Related Articles
External References
- LearnSQL.com. "How Do You Get Rid of Duplicates in an SQL JOIN?" learnsql.com
- PostgreSQL Mailing List Archives. "Weird Join Producing Too Many Rows." postgresql.org
- Grio Blog. "An Intro to Resolving Duplicate Results in SQL Queries." blog.grio.com
){kind=link}
0 Comments