A self join connects a table to itself using two aliases. It sounds strange until you meet hierarchical data — employees and managers, categories and parent categories — where it becomes the natural tool.
Key Points
- Syntax uses aliases: FROM employees e JOIN employees m ON e.manager_id = m.id
- Classic use: list each employee with their manager's name
- Find duplicates: join on the same value but different ids
- Compare rows: pair each order with the previous order of the same customer
- Hierarchies deeper than one level often need recursive CTEs instead
- Self joins are conceptually just normal joins — the table appears twice