Why does my join return duplicate rows?
Understand the concept
A join returns one output row for each matching combination, so repeated parent values are often the correct result of a one-to-many relationship. Unexpected duplicates usually mean the query's relationship or desired result level was misunderstood. The fix is to align the join or aggregation with whether the output should represent a customer, an order or an order item.
Important ideas
-
One row may match several rows in the other table.
-
Check relationship cardinality and join conditions.
-
Aggregate the many-side first if needed; do not add
DISTINCTwithout understanding the cause. -
Joining two separate one-to-many child tables can multiply their rows and overcount sums from either side.
Can you explain it without the notes?
Why does my join return duplicate rows?
Use your own words. Explain an example and one limitation before checking the explanation.
Check your explanation
A join returns one output row for each matching combination, so repeated parent values are often the correct result of a one-to-many relationship. Unexpected duplicates usually mean the query's relationship or desired result level was misunderstood. The fix is to align the join or aggregation with whether the output should represent a customer, an order or an order item.
Turn this concept into a clear answer.
- State the intended grain of the result, such as “one row per customer,” before using DISTINCT or GROUP BY.
Keep connecting the ideas.
Next lesson: Why does a LEFT JOIN behave like an INNER JOIN?Adapted from 05_SQL_POSTGRESQL.md · “Why does my join return duplicate rows?”. Examples are study fragments; framework behavior can depend on your version.