A Self JOIN can compare values from one row against another row in the same table.
Row-to-row comparisons are useful when identifying changes, duplicates, relationships, or differences within one dataset.
Compare employees in the same department to identify salary differences.
SELECT
e1.EmployeeName AS Employee1,
e2.EmployeeName AS Employee2,
e1.Salary AS Salary1,
e2.Salary AS Salary2
FROM Employees e1
INNER JOIN Employees e2
ON e1.DepartmentID = e2.DepartmentID
AND e1.EmployeeID < e2.EmployeeID
WHERE e1.Salary <> e2.Salary;Used in salary analysis, duplicate detection, historical comparison, and relationship analysis.
- Self JOIN enables row-to-row comparison.
- Aliases identify each row reference.
- A comparison condition prevents self-matching.
- Additional conditions can restrict comparisons.
- Large tables may create many row combinations.
Why use a Self JOIN for row comparison?
It allows values from one row to be compared directly with values from another row in the same table.
What is a risk of Self JOIN on large tables?
It can generate many combinations of rows and become expensive if the JOIN conditions are not selective.