Beginner

636. Self JOIN for Duplicate Detection

Use a Self JOIN to identify possible duplicate records within the same table.

📘 Lesson 636 of 728 ⏱ 14 min 🎯 Advanced

Duplicate detection with a Self JOIN compares records against other records using matching business attributes while excluding the same row.

Duplicate records can cause inaccurate reporting, customer counts, and transaction analysis.

Find employees with the same email address.

SQL 
SELECT
    e1.EmployeeID AS Employee1,
    e2.EmployeeID AS Employee2,
    e1.Email
FROM Employees e1
INNER JOIN Employees e2
    ON e1.Email = e2.Email
   AND e1.EmployeeID < e2.EmployeeID;

Used in data quality, customer master cleanup, employee validation, and migration projects.

  • Compare business-identifying columns.
  • Exclude the same row.
  • Use EmployeeID < EmployeeID to avoid duplicate pairs.
  • Duplicate detection depends on business rules.
  • NULL values require special consideration.
How can a Self JOIN find duplicates?

JOIN rows using the suspected duplicate columns and exclude the same record using a unique identifier comparison.

Why is a unique ID useful in duplicate detection?

It allows the query to distinguish records and prevent a row from matching itself.

Have you finished this lesson?

Introduction to SQL – All Lessons