Duplicate Rows

Definition

Joins can introduce duplicate-looking rows into a result — not literal duplicate rows in the stored data, but repeated values from one side of a one-to-many (or fan-out) relationship, which is a direct, expected consequence of join cardinality (16.10), not a bug in the join itself.

How It Works

sql
SELECT d.name AS department, e.name AS employee
FROM departments d
JOIN employees e ON e.department_id = d.id;

If Engineering has two employees (Asha, Rohan), "Engineering" appears TWICE in the result — once per matching employee. This is entirely correct and expected: each output ROW represents one (department, employee) PAIR, and there are genuinely two such pairs for Engineering.

Edge Cases and Pitfalls

• Seeing a repeated value in a join's output is not automatically a bug — check whether the REPETITION makes sense given the relationship's cardinality before assuming something is wrong.
• If TRUE duplicate rows (identical across every selected column) are the actual problem — e.g. because a join predicate unintentionally matched more broadly than intended — DISTINCT (12.2) removes them, but treating the symptom with DISTINCT without understanding WHY duplicates appeared risks papering over an actual join-condition bug rather than fixing it.
• A join predicate that's supposed to be one-to-one but isn't (due to a data-quality issue, like two rows in the "one" side accidentally sharing the same key) can silently produce more rows than expected — this looks identical to intended one-to-many duplication but is actually a data integrity problem worth investigating rather than a normal join outcome.

Key Takeaways

• Repeated-looking values in a join's output are usually the expected, correct consequence of one-to-many cardinality, not a bug.
• DISTINCT can remove genuine duplicate rows, but should follow understanding WHY they appeared, not replace that understanding.
• Unexpected duplication can also signal a real data-quality problem (an unintended one-to-many where one-to-one was assumed) — worth investigating rather than blindly de-duplicating.