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.

Get started - freeMid and Senior open as you complete Junior projects
Data Engineersqlduckdbjoins

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

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 - free

What you'll practice

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

Fix a SQL COUNT GROUP BY Double-Counting BugData projectSQL Query Foreign Key: Handle Orphan RecordsData projectHow to Query Parquet Files With DuckDBData projectData roadmapStep by step to hiredData interview questionsSTAR answersAll Data projectsProjects hub

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
Get started - free