How to use self joins

Short answer

A self join joins a table to itself using different aliases, enabling queries about relationships between rows in the same table.

Steps

1. Assign aliases: SELECT e.name, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id;
2. Use LEFT JOIN to include rows without a match, such as the CEO with no manager.
3. Find pairs: SELECT a.name, b.name FROM employees a JOIN employees b ON a.department = b.department AND a.id != b.id;
4. Filter by hierarchy depth with recursive CTEs for deep trees.
5. Ensure indexes exist on the joined columns.

Tips

• Self joins are useful for organizational hierarchies, graph adjacency lists, and finding duplicates.
• Recursive CTEs are often a better choice for traversing arbitrarily deep hierarchies.

Common issues

• Missing a.id != b.id in pair queries causes every row to match itself.
• NULL foreign keys in hierarchical data break inner self joins unexpectedly.r