Self-Join — Org Chart / Hierarchies

-- Employee → manager pairs (each employee with their direct boss)
SELECT e.id, e.name AS employee, m.name AS manager
FROM   employees e
LEFT   JOIN employees m ON m.id = e.manager_id
ORDER  BY m.name, e.name;

-- Find people who have the same job title
SELECT a.name, b.name, a.title
FROM   employees a
JOIN   employees b ON a.title = b.title AND a.id < b.id
ORDER  BY a.title;

-- People who report to the same manager
SELECT a.name AS person_a, b.name AS person_b, m.name AS manager
FROM   employees a
JOIN   employees b ON a.manager_id = b.manager_id AND a.id < b.id
JOIN   employees m ON m.id = a.manager_id;

-- Find duplicates by email (self-join trick)
SELECT a.id, b.id, a.email
FROM   users a
JOIN   users b ON a.email = b.email AND a.id < b.id;