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
ManagerID columns. The
ManagerID column value will be any of the
Thus, we can find the manager's information in the same table.
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.