Self Joins and Hierarchies

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;

# Resolve a manager relationship stored in the same table.

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;

# Compare rows in the same table using inequality to avoid mirrored duplicates.

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;

# Generate pairs of rows that share a date.

One-level lookup: self join
Deep tree traversal: recursive CTE

# Great for direct parent relationships like employee → manager.