Self Joins in SQL Server

Self Joins in SQL Server- Find the Manager Name for each employee in the employee table

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 Emp

(
empid int primary key,
name varchar(50),
mgrid int
)

Insert into Emp(empid,name,mgrid) values (1001,'Manish Agrahari',1001); 

Insert into Emp(empid,name,mgrid) values (1002,'Deepti',1001);

Insert into Emp(empid,name,mgrid) values (1003,'Amit',1001);

Insert into Emp(empid,name,mgrid) values (1004,'Sandy',1002);

Insert into Emp(empid,name,mgrid) values (1005,'Ankit',1003);

Insert into Emp(empid,name,mgrid) values (1006,'Kapil',1002);

Select * from Emp;

select e.empid,e.name,m.name as 'mgr name' from Emp e, Emp m where e.mgrid =m.empid

select e.empid,e.name,m.name as 'mgr name' from Emp e, Emp m where m.empid = e.mgrid

Fetch the Managers

SELECT DISTINCT e1.EmpId, e1.name FROM Emp e1, Emp e2 where e1.EmpId=e2.mgrid; 

Fetch the Employee who has Managers

SELECT DISTINCT e2.EmpId, e2.name FROM Emp e1, Emp e2 where e1.EmpId=e2.mgrid; 

Thanks
~ Suraj K.