SELF JOIN in Sql Server.

SELF JOIN is not a different type of JOIN. A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition.  It can be classified under any type of JOIN - INNER, OUTER or CROSS Joins. The above query is, LEFT OUTER SELF Join.Let's see how to use self join 

Step 1: Create a table

Create table Employees
(
Empid int primary key,
EmpName varchar(50),
ManagerId int

)

Step 2 : Insert data into Employees table

Insert into Employees values(1,'Ram',1),

 (2,'Deepa',1),

 (3,'Amit',1),

 (4,'Sandy',null),

 (5,'Ankit',3),

 (6,'Kapil',2),

 (7,'Kapil',2)

Consider Employees table shown below.

Let's say I want to get the data like below.

Select E.EmpName as Employee, M.EmpName as Manager

from Employees E

Left Join Employees M

On E.ManagerId = M.Empid

After executing the above query.the output will be above image

Using INNER JOIN

Select E.EmpName as Employee, M.EmpName as Manager

from Employees E

Inner Join  Employees M

On E.ManagerId = M.Empid

After executing the above query.the output will be like below:

Using CROSS JOIN

Select E.EmpName as Employee,M.EmpName  as Manager

from Employees E

Cross Join Employees M