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
Table of Contents
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
- Customer account in the bank with an introducer. The introducer must be a customer of the bank.
- An employee with a supervisor or manager.
- 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.
1 2 3 4 5 6 7 | 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,
1 2 3 4 5 6 | 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
1 2 3 4 5 6 7 8 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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.
1 2 3 4 5 6 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | 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
- Join the Dept table with the employee table to get the name of the dept. (based on DeptID)
- Get the ManagerID from DeptManager table joining it to the above result (based on DeptID)
- To get the Manager’s name Join the Employees table again on the ManagerID & EmployeeID
1 2 3 4 5 6 7 8 9 10 | 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.
1 2 3 4 5 6 7 8 9 10 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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
Summary
In this article, you learned what is Self Join is and how to use it SQL Server with several examples
