Microsoft SQL Server 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:

IDNameBoss_ID
1Bob3
2Jim1
3Sam2

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:

NameBoss
BobSam
JimBob
SamJim