Self Join by Example in SQL SERVER

Self Join is a join where a table is joined to itself. That is, both the tables in the join operations are the same. In a self join each row of the table is joined with itself if they satisfy the join condition

Examples of Self Join

There are many instances, where you need to self join a table. Usually when the table has a parent-child relationship with itself. In a parent-child relationship, the table will have FOREIGN KEY which references its own PRIMARY KEY.

For Example

1. Customer account in the bank with an introducer. The introducer must be a customer of the bank.
2. An employee with a supervisor or manager.
3. Department under another Department

Self Join Syntax

There is no special syntax for Self-join. It is just a normal join where first_table and second_table refers to the same table.

SELECT [colums]
FROM first_table 
[join_type] JOIN 
first_table 
[ON (join_condition)]

• There is no special self Join Syntax. There is no SELF JOIN keyword
• The Alias for the table is a must as both tables are the same. Otherwise, it will result in an error
• You can use the same table at multiple levels

For Example,

Select t1.column as column1, t2.column as column2 
from table t1
left join table t2
on (t1.id=t2.someID)

The table is joined with itself thrice

Select t1.column as column1, t2.column as column2, t3.column as column3
from table t1
left join table t2
on (t1.id=t2.someID)
left join table t3
on (t1.id=t3.someOtherID)

Self Join Example

Consider the following table Employees. It has a ManagerID column, which refers to its own EmployeeID. ManagerID Represents the EmployeeID of the Manager. Note that ManagerID int null as the topmost employee will not have any manager above him

create table Employees (
    EmployeeID int primary key,
    Name varchar(100),
    ManagerID int null
    CONSTRAINT FK_ManagerID FOREIGN KEY (ManagerID)
         REFERENCES Employees (EmployeeID)
);

insert into Employees Values(1,'Kevin ',null)
insert into Employees Values(2,'Akshay',1)
insert into Employees Values(3,'Sandeep',2)
insert into Employees Values(4,'Swati',2)
insert into Employees Values(5,'Sunil',1)

To get the Employees List, with the manager’s name we need to join the Employees table with itself as shown below.

Select employee.EmployeeID,employee.Name, manager.EmployeeID as ManagerID, manager.Name As Manager
from Employees employee
left join Employees manager
on (employee.ManagerID=manager.EmployeeID)

More Examples

Now, let us consider another sample database, where the employee’s table has the DeptID column. The Manager of the department is stored in the third table DeptManager.

create table Dept (
   DeptID int primary key,
   Name varchar(100),
)

create table Employees (
   EmployeeID int primary key,
   Name varchar(100),
   DeptID int null,
   CONSTRAINT FK_Employees_DeptID   FOREIGN KEY (DeptID) REFERENCES Dept (DeptID)
);

create table DeptManager (
   DeptID int ,
   ManagerID int,
   primary key (DeptID, ManagerID),
   CONSTRAINT FK_DeptManager_DeptID   FOREIGN KEY (DeptID) REFERENCES Dept (DeptID),
   CONSTRAINT FK_DeptManager_ManagerID   FOREIGN KEY (ManagerID) REFERENCES Employees (EmployeeID)
)

insert into Dept Values(1,'Excutive')
insert into Dept Values(2,'HR')

insert into Employees Values(1,'Kevin ',1)
insert into Employees Values(2,'Akshay',1)
insert into Employees Values(3,'Sandeep',2)
insert into Employees Values(4,'Swati',2)
insert into Employees Values(5,'Sunil',2)

insert into DeptManager Values(1,1)
insert into DeptManager Values(2,2)

To get the employee list with dept name with the manager, we need to join

1. Join the Dept table with the employee table to get the name of the dept. (based on DeptID)
2. Get the ManagerID from DeptManager table joining it to the above result (based on DeptID)
3. To get the Manager’s name Join the Employees table again on the ManagerID & EmployeeID

Select employee.EmployeeID,employee.Name, dept.Name, manager.EmployeeID as ManagerID, manager.Name As Manager
from Employees employee
left join Dept dept
on (employee.DeptID =dept.DeptID)
left join DeptManager deptManager
on (employee.DeptID = deptManager.DeptID)
left join Employees Manager
On ( Manager.EmployeeID=deptManager.ManagerID)

Now, if you notice the Employee Kevin is Manager of Executive Dept. Hence shown as Manager to himself, which is wrong. You can correct by ensuring that employeeID of the manager & employee cannot be the same (employee.employeeID <> Manager.EmployeeID) as shown in the query below.

Select employee.EmployeeID,employee.Name, dept.Name, manager.EmployeeID as ManagerID, manager.Name As Manager
from Employees employee
left join Dept dept
on (employee.DeptID =dept.DeptID)
left join DeptManager deptManager
on (employee.DeptID = deptManager.DeptID)
left join Employees Manager
On ( Manager.EmployeeID=deptManager.ManagerID and employee.employeeID <> Manager.EmployeeID)

You can extend the above query, one step further by asking for Managers Department and his manager. That requires us to join the employee & dept table again as shown below

Select employee.EmployeeID,employee.Name, dept.Name, manager.EmployeeID as ManagerID, manager.Name As Manager, ManagerDept.Name As ManagerDept, TopManager.Name as SrManager
from Employees employee
left join Dept dept
on (employee.DeptID =dept.DeptID)
left join DeptManager deptManager
on (employee.DeptID = deptManager.DeptID)
left join Employees Manager
On ( Manager.EmployeeID=deptManager.ManagerID  and employee.employeeID <> Manager.EmployeeID)
left join Dept ManagerDept
On ( ManagerDept.DeptID=Manager.DeptID)
left join DeptManager ManagerDeptManager
On ( ManagerDeptManager.DeptID=ManagerDept.DeptID)
left join Employees TopManager
On ( TopManager.EmployeeID=ManagerDeptManager.ManagerID  and Manager.employeeID <> TopManager.EmployeeID)

References

1. Join Syntax (SQL Server)
2. Sample database

Summary

In this article, you learned what is Self Join is and how to use it SQL Server with several examples