Self Joins In SQL Server

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. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition.

For example the following query Returns Employee Names and their Manager Names for whom they are working.

Create table Employee

(

Empid int primary key,

Name varchar(100),

Mngrid int

)

Insert into Employee(Empid,Name,Mngrid)

values (101,'Jitu Yadav',101);

Insert into Employee(Empid,Name,Mngrid)values (102,'Nelima',101);

Insert into Employee(Empid,Name,Mngrid)values (103,'Sumit',101);

Insert into Employee(Empid,Name,Mngrid)values (104,'Santosh',102);

Insert into Employee(Empid,Name,Mngrid)values (105,'Sachin',103);

Insert into Employee(Empid,Name,Mngrid)values (106,'Hemant',102);

Select * from Employee

SELECT E.empid, E.name, M.name  "Manager" FROM Employee E, Employee M WHERE E.Mngrid=M.Empid;