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.

CREATE table mtblEmployee1
(Employee_Id int identity (1,1),
Employee_Name nvarchar(50),
Supervisor_Id int
)

Now insert some value in this table

INSERT INTO mtblEmployee
SELECT 'Amit Mittal',0
UNION ALL
SELECT 'Piyush Sharma',3
UNION ALL
SELECT 'Dinesh Aggarwal',6
UNION ALL
SELECT 'Shobhit Roy',5
UNION ALL
SELECT 'Varun Dhiman',1

fetch the records

select * from mtblEmployee

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

The below both query will give the same result

SELECT a.Employee_Id as Id, a.Employee_Name as Employee,
b.Employee_Name as Supervisor
FROM mtblEmployee a
INNER JOIN mtblEmployee b
on a.Supervisor_Id=b.Employee_Id

OR

SELECT a.Employee_Id as Id, a.Employee_Name as Employee,
b.Employee_Name as Supervisor
FROM mtblEmployee a, mtblEmployee b
where a.Supervisor_Id=b.Employee_Id

Result:


I hope it would help.

Thanks