SQL Join Returning Duplicate Rows? Find the Missing Join Condition
A query that should return 1,000 orders returns 100,000, and each order is paired with the wrong customer. The cause is a join with no condition, which pairs every row with every row. An explicit JOIN ... ON and a row-count check catch it.
The symptom: row counts multiply
SELECT o.id, o.total, c.name
FROM orders o, customers c;
1,000 orders times 100 customers is 100,000 rows. The comma join with no
WHERE condition is a Cartesian product (a cross join). The output looks
plausible - real ids, real names - which is why it survives review.
Write the join explicitly
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;
With JOIN ... ON, a missing condition is a syntax error instead of a silent
cross join. Prefer it over comma joins everywhere.
Other causes of duplicate rows after a join
- The right side is not unique on the join key. Joining orders to a
customer_addressestable with two addresses per customer doubles those orders. Check withGROUP BY key HAVING count(*) > 1. - Joining on the wrong column, such as
o.id = c.id. - Joining two one-to-many tables to the same parent, which multiplies both sides.
Always compare row counts
Before trusting a joined result, compare its row count with the table that defines the grain (one row per order, here). If the numbers differ, find out why before anyone sums a column.
What you do in the project
You run the query in DuckDB, see 100,000 rows, rewrite the join, and confirm exactly 1,000 rows with each order matched to its own customer.
Do real production work, free. Start with Junior projects in a live cloud workspace - every project you complete lands on your portfolio.
Get started - freeWhat you'll practice
- Recognizing a cross join from multiplied row counts
- Rewriting a comma join as an explicit JOIN ... ON
- Checking join keys for duplicates before trusting a result
FAQ
Why does my SQL join return duplicate rows?
Usually because the join condition is missing or wrong (creating a cross join), or because the table you join to has more than one row per join key. Compare the result row count with the source table to find out which.
What is a Cartesian product in SQL?
It is every row of one table paired with every row of another, which is what a join without a condition produces. 1,000 rows joined to 100 rows gives 100,000 rows.
Should I use DISTINCT to remove duplicate rows from a join?
Not as a first step. DISTINCT hides the real cause and can drop rows that should be there. Find the missing condition or the non-unique key instead.
Keep learning
Do real production work, free. Start with Junior projects in a live cloud workspace - every project you complete lands on your portfolio.
Get started - free