Beginner

573. Self JOIN for Comparison

Learn how Self JOIN can compare rows from the same table.

📘 Lesson 573 of 728 ⏱ 13 min 🎯 Intermediate

A Self JOIN can pair rows within the same table so that values from one row can be compared with values from another related row.

Comparing records within the same dataset can reveal relationships, differences, and potential business patterns.

Find employees in the same department who have different employee IDs.

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

Used for peer comparisons, duplicate analysis, relationship detection, and employee analysis.

  • Self JOIN can compare rows within one table.
  • Aliases represent different rows.
  • Comparison conditions prevent unwanted pairs.
  • EmployeeID < EmployeeID avoids reverse duplicates.
  • It can be useful for relationship analysis.
Why use EmployeeID < EmployeeID in a Self JOIN?

It ensures each pair is returned once instead of returning both employee combinations.

Can Self JOIN identify related records?

Yes. It can compare rows and identify relationships based on common or different column values.

Have you finished this lesson?

Introduction to SQL – All Lessons