What is a self join, and when would you use one?
Quick Answer
A self join is joining a table to itself, using two different aliases. It's used when rows in a table relate to other rows in the same table — the classic case is an Employees table where each row has a ManagerId that points at another employee.
A self join is when you join a table to itself with two aliases, and you need it whenever rows in a table reference other rows in the same table. The textbook example is an employees table with a manager id column — you join Employees e to Employees m on e.ManagerId = m.EmployeeId to get each employee alongside their manager's name, using a left join so the top of the tree still shows up. If I need the whole chain of command, not just one level, I'd switch to a recursive CTE.
SELECT e.Name AS Employee, m.Name AS Manager FROM Employees e LEFT JOIN Employees m ON e.ManagerId = m.EmployeeId;
- You must alias the table twice so the engine treats them as separate row sources.
- Use a
LEFT JOINif some rows have no partner (e.g. the CEO has no manager). - For a full hierarchy of arbitrary depth, use a recursive CTE instead of chained self joins.
- Also handy for “find pairs” queries — e.g. products in the same category, or consecutive rows.
SELECT e.Name AS Employee, m.Name AS Manager
FROM dbo.Employees e
LEFT JOIN dbo.Employees m ON m.EmployeeId = e.ManagerId
ORDER BY m.Name, e.Name;