Microsoft SQL Server Join Self Join
Example
A table can be joined onto itself in what is known as a self join, combining records in the table with other records in the same table. Self joins are typically used in queries where a hierarchy in the table's columns is defined.
Consider the sample data in a table called Employees:
| ID | Name | Boss_ID |
|---|---|---|
| 1 | Bob | 3 |
| 2 | Jim | 1 |
| 3 | Sam | 2 |
Each employee's Boss_ID maps to another employee's ID. To retrieve a list of employees with their respective boss' name, the table can be joined on itself using this mapping. Note that joining a table in this manner requires the use of an alias (Bosses in this case) on the second reference to the table to distinguish itself from the original table.
SELECT Employees.Name,
Bosses.Name AS Boss
FROM Employees
INNER JOIN Employees AS Bosses
ON Employees.Boss_ID = Bosses.ID
Executing this query will output the following results:
| Name | Boss |
|---|---|
| Bob | Sam |
| Jim | Bob |
| Sam | Jim |
