I have already explain regarding left join in SQL,Right Join in SQL Server, Inner Join in SQL Server,RANK(), DENSE_RANK(), ROW_NUMBER() and Aggregate functions in sql server. Today I am going to explain regarding Self Join in Sql server.

As the name itself suggest, a self join is nothing more than the joining the table to itself. So using a self join you can get a result that join records in a table with other records in same table.  So to have a same table in one query you must have to define some alias to at least one table.

So lets go through the example to have a look at the self join.

Suppose i have a employee table as listed below.

create table Employee
(
  Id INT Identity,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  ManagerId int
)

INSERT INTO Employee(FirstName,LastName,ManagerId)
values('Lida','Best',1)
INSERT INTO Employee(FirstName,LastName,ManagerId)
values('Joy','Crossman',1)
INSERT INTO Employee(FirstName,LastName,ManagerId)
values('Latisha','Dinkle',1)
INSERT INTO Employee(FirstName,LastName,ManagerId)
values('Steven','Gehrmann',4)
INSERT INTO Employee(FirstName,LastName,ManagerId)
values('Deloras','Geske',4)
INSERT INTO Employee(FirstName,LastName,ManagerId)
values('Bruce','Harmer',4)
INSERT INTO Employee(FirstName,LastName,ManagerId)
values('Russ','Hixon',4)

Now the “ManagerId” column is used to assign manager to each employee and a manager him self is also a employee.

So to create a self join of above table use below query.

select e1.Id,e1.FirstName,e1.LastName,e2.FirstName as 'Manager'
from Employee e1,Employee e2
where e1.ManagerId = e2.Id;

Now to get a user friendly result, I modified above query.

select e1.Id,e1.FirstName,e1.LastName,
case when e1.id = e2.ManagerId then '-'
else e2.FirstName + ' ' + e2.LastName end as 'Manager'
from Employee e1,Employee e2
where e1.ManagerId = e2.Id

So below is the screen shot of above query result.

To see the both of the above query in action,

Please visit my sql fiddle at : http://sqlfiddle.com/#!3/b3e9e/3