Self Join
SQL · Joining Multiple Tables
Theory
A self join joins a table to itself. It is not a special keyword: you list the same table twice with different aliases and treat each copy as a separate role. The classic case is an employees table where manager_id points to another row's id; joining employees e to employees m ON e.manager_id = m.id puts each employee next to their manager's name.
Any join type can be a self join. An INNER self join drops the top manager (whose manager_id is NULL); a LEFT self join keeps them with a NULL manager. Self joins also compare rows within one table: pairs of employees in the same city (with e1.id < e2.id to avoid duplicate pairs), or each day's sales next to the previous day's. For deep hierarchies of unknown depth, recursive CTEs are the better tool.
Why we use it
Some relationships live inside one table, like employee to manager or product to parent product. A self join lets you read both ends of that relationship in a single row.
When to use it
Use a self join for one level of a hierarchy or for comparing rows of the same table. For many levels, use a recursive CTE. For comparing a row with its previous or next row, window functions like LAG are usually simpler.
How it works
- Alias the table twice: e (employee) and m (manager)
- Take a row from e
- Look for an m row where m.id = e.manager_id
- Is there a match? Yes: Output employee with manager. No: Drop (INNER) or keep with NULL (LEFT).
- Return employee-manager pairs
Syntax
SELECT a.col, b.col
FROM table a
JOIN table b ON a.key = b.key;Example
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;Key points
- Requires table aliases to distinguish the two roles
- Common for org charts and manager/employee relationships
- INNER self join drops the top of the hierarchy; LEFT keeps it
- Use e1.id < e2.id to avoid duplicate and mirror pairs
- Deep hierarchies are better handled with recursive CTEs