Self joins

This lesson uses the unmodified DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Use two aliases of Employees to show each person beside their manager.

Run the example

SELECT TOP (5) e.EmployeeID, e.FirstName AS EmployeeName,
       m.FirstName AS ManagerName
FROM dbo.Employees AS e
LEFT JOIN dbo.Employees AS m ON m.EmployeeID = e.ManagerID
ORDER BY e.EmployeeID;

Read it step by step

1. e and m are separate aliases for the same Employees table.
2. e.ManagerID references the employee row represented by m.
3. LEFT JOIN retains employees with no manager, showing NULL for ManagerName.

Check the result

A NULL ManagerName here means no manager row was linked; it is not an empty string.

Practice

Show the first three employees who have a manager, with EmployeeID and their manager’s FirstName.

SELECT TOP (3) e.EmployeeID, m.FirstName AS ManagerName
FROM dbo.Employees AS e
JOIN dbo.Employees AS m ON m.EmployeeID = e.ManagerID
ORDER BY e.EmployeeID;