What is a Self Join? Explain with Example – Interview Question of the Week #064

Question: What is a self join? It is an ordinary join in which the same table appears in two roles. Separate aliases identify the roles, such as employee and manager.

This is a popular developer interview question across programming languages. My favorite practical example has employees and their managers in one table: a manager is also an employee, so another table is not needed just to find the manager’s name.

DECLARE @Employee TABLE(EmployeeID int PRIMARY KEY,
 Name nvarchar(50),ManagerID int NULL);
INSERT @Employee VALUES(1,N'Mike',3),(2,N'David',3),(3,N'Roger',NULL),
 (4,N'Marry',2),(5,N'Joseph',2),(7,N'Ben',2);
SELECT EmployeeID,Name,ManagerID FROM @Employee ORDER BY EmployeeID;
SELECT e.Name AS EmployeeName,m.Name AS ManagerName
FROM @Employee AS e INNER JOIN @Employee AS m
 ON e.ManagerID=m.EmployeeID ORDER BY e.EmployeeID;
SELECT e.Name AS EmployeeName,
 CASE WHEN e.ManagerID IS NULL THEN N'Top Manager'
      WHEN m.EmployeeID IS NULL THEN N'Missing manager row'
      ELSE m.Name END AS ManagerName
FROM @Employee AS e LEFT JOIN @Employee AS m
 ON e.ManagerID=m.EmployeeID ORDER BY e.EmployeeID;

INNER JOIN finds the matching managers

Mike and David report to Roger. Marry, Joseph and Ben report to David. The inner join returns those five rows because each has a matching manager row. Roger has no manager, so he is absent.

LEFT JOIN keeps Roger too

The left join preserves every employee row. Roger now appears as Top Manager. The revised CASE distinguishes a NULL ManagerID from a non-NULL ID with no matching row, which would otherwise be mislabeled as the top manager.

A self join can use INNER, LEFT, RIGHT, FULL or CROSS JOIN as appropriate. It does not require duplicate data in one column; the employee’s ManagerID is matched to the manager’s EmployeeID here. A foreign key can enforce valid references, while the join answers the reporting question.

A self join retrieves one relationship level; traversing an entire management hierarchy is a separate recursive-query task.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.