Self-Join in SQL Server

In SQL Server, the self-join is like a regular join, but it joins a table to itself. Similar to any other join, a self-join requires at least two tables. But instead of adding a different table to the join, you add another instance of the same table. It is a join between two copies of the same table. Self-join is mainly used for querying the hierarchical data stored in a single table.

There is no Self Join keyword. You write a normal join where both the tables involved in the join are the same.

The following is the syntax of the self-join query.

SELECT a.column1, b.column2
FROM table1 a, table1 b
WHERE condition;

In the self-join, you have to specify the table alias to both the instance of a table; else you will get an error.

Consider the following Employee table that contains EmployeeID and ManagerID columns. The ManagerID column value will be any of the EmployeeID. Thus, we can find the manager's information in the same table.

The Employee table contains the following data.

In the above table, EmployeeID 7 contains ManagerID 1 as his manager. EmployeeID 1 contains NULL in the ManagerID column as he doesn't have a manager.

The following self-join query gets the managers of all the employees.

SELECT emp.FirstName + ',' + emp.LastName as Employee, 
mgr.FirstName + ',' + mgr.LastName as Manager
FROM Employee emp
INNER JOIN Employee mgr ON emp.ManagerID = mgr.EmployeeID;

The above query would display the following result in SQL Server.

The above result displays all employees having a manager along with their manager name. Employees who do not have a manager (ManagerID column is NULL) are not displayed.

You can tweak the above query to list all employees even if they do not have a manager using the LEFT JOIN query.

SELECT emp.FirstName + ',' + emp.LastName as Employee,
mgr.FirstName + ',' + mgr.LastName as Manager
FROM Employee emp
LEFT JOIN Employee mgr ON emp.ManagerID = mgr.EmployeeID

The above query would display the following result.