SQL self join examples for managers, duplicates, comparisons, and basic hierarchy modeling.
Join a table to itself for hierarchies and comparisons.
Resolve a manager relationship stored in the same table.
SELECT e.employee_id,
e.name AS employee_name,
m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id;Alias the same table twice so each role is clear.
Compare rows in the same table using inequality to avoid mirrored duplicates.
SELECT a.user_id, b.user_id, a.email
FROM users a
JOIN users b
ON a.email = b.email
AND a.user_id < b.user_id;The `<` condition avoids returning both `(a,b)` and `(b,a)`.
SELECT a.id, b.id, a.event_date
FROM events a
JOIN events b
ON a.event_date = b.event_date
AND a.id < b.id;Useful for pairing or clash detection inside one table.
When a self join is enough and when you need more.
Great for direct parent relationships like employee → manager.
One-level lookup: self join
Deep tree traversal: recursive CTEA single self join handles immediate parent or sibling checks. For full ancestor paths, use recursive CTEs.