A SELF JOIN is a join where a table is joined with itself.

Useful for comparing rows within the same table, such as hierarchical or relational data.

You must use table aliases to distinguish between the “left” and “right” instances of the table.

Basic syntax:

SELECT a.column1, b.column2
FROM table_name AS a
JOIN table_name AS b
ON a.common_column = b.common_column;

Step 1: Example table

Employees Table

EmployeeID Name ManagerID
1 John NULL
2 Jane 1
3 Mike 1
4 Sara 2
5 Tom 3

Here, ManagerID refers to the EmployeeID of another employee (the manager).

Step 2: Simple SELF JOIN

Retrieve employees with their managers’ names:

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees AS e
LEFT JOIN Employees AS m
ON e.ManagerID = m.EmployeeID;

Result:

Employee Manager
John NULL
Jane John
Mike John
Sara Jane
Tom Mike

Explanation:

  • e → Represents employees
  • m → Represents managers
  • LEFT JOIN ensures employees without a manager (John) are included.

Step 3: SELF JOIN for hierarchy

You can find employees reporting to a specific manager:

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees AS e
JOIN Employees AS m
ON e.ManagerID = m.EmployeeID
WHERE m.Name = 'John';

Result:

Employee Manager
Jane John
Mike John

Only employees directly reporting to John are shown.

Step 4: SELF JOIN with multiple levels

You can join more than two instances to find extended hierarchy:

SELECT e.Name AS Employee, m.Name AS Manager, gm.Name AS GrandManager
FROM Employees AS e
LEFT JOIN Employees AS m ON e.ManagerID = m.EmployeeID
LEFT JOIN Employees AS gm ON m.ManagerID = gm.EmployeeID;

Result:

Employee Manager GrandManager
John NULL NULL
Jane John NULL
Mike John NULL
Sara Jane John
Tom Mike John

Shows employee → manager → grandmanager relationships.

Step 5: Important Notes

  • Always use aliases (e, m, etc.) to distinguish table instances.
  • Can be used with INNER JOIN, LEFT JOIN, RIGHT JOIN depending on whether you want to include unmatched rows.
  • Useful for hierarchical, parent-child, or relational comparisons within the same table.

Categorized in:

SQL Server,