Why does my join return duplicate rows?

Definition:

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.

Key points:

• 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 DISTINCT without understanding the cause.

• Joining two separate one-to-many child tables can multiply their rows and overcount sums from either side.

Interview point:

• State the intended grain of the result, such as “one row per customer,” before using DISTINCT or GROUP BY.