Use of 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 employees and their supervisors, 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 mtblEmployee
(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