Why We Need Self Join in SQL

Suppose we have an Employee table. All the employees and their supervisor (or Manager) are in the same table. If we want to fetch the employee and their supervisor, how we could get them?

For that you need to choose self join. Self join is an inner join but only applied with one table. For applying self join we need 2 instance of the same table in memory. We have to use alias for making identical copies of the table because there is only one table.

See the example below. Lets create a employee table first.

  1. CREATE table mtblEmployee1  
  2. (Employee_Id int identity (1,1),  
  3. Employee_Name nvarchar(50),  
  4. Supervisor_Id int  
  5. )  
Now insert some value in this table
  1. INSERT INTO mtblEmployee  
  2. SELECT 'Amit Mittal',0  
  3. UNION ALL   
  4. SELECT 'Piyush Sharma',3  
  5. UNION ALL   
  6. SELECT 'Dinesh Aggarwal',6  
  7. UNION ALL  
  8. SELECT 'Shobhit Roy',5  
  9. UNION ALL  
  10. SELECT 'Varun Dhiman',1
fetch the records
  1. select * from mtblEmployee  


By using self join we can fetch the employee and their supervisors

The below both query will give the same result
  1. SELECT a.Employee_Id as Id, a.Employee_Name as Employee,  
  2. b.Employee_Name as Supervisor  
  3. FROM mtblEmployee a   
  4. INNER JOIN mtblEmployee b  
  5. on a.Supervisor_Id=b.Employee_Id
OR
  1. SELECT a.Employee_Id as Id, a.Employee_Name as Employee,  
  2. b.Employee_Name as Supervisor   
  3. FROM mtblEmployee a, mtblEmployee b   
  4. where a.Supervisor_Id=b.Employee_Id  
Result:


I hope it would help.

Thanks