Self JOIN in MySQL: Use Cases and Examples

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