Comparing Rows Within One Table
Self-join patterns for finding pairs, duplicates, and adjacent records.
Self Joins for Row-to-Row Comparison
Beyond hierarchies, the other major use of a self join is comparing rows of the same table to each other. Instead of parent-child, you pair arbitrary rows to find duplicates, near-matches, or adjacent records.
The pattern is the same: alias the table twice and write an ON condition that expresses the relationship between the two rows you want to pair.
Finding Pairs in the Same Group
Classic question: find all pairs of employees who work in the same department. Join the table to itself on equal department, but keep the two rows distinct.
The naive join would also pair every employee with themselves and produce each pair twice. We fix that next.
SELECT a.name, b.name, a.department
FROM employees a
JOIN employees b ON a.department = b.department;All lessons in this course
- CROSS JOIN and Cartesian Products
- SELF JOIN for Hierarchies
- Comparing Rows Within One Table
- Choosing the Right Join Type