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  "Manager" FROM Emp e, Emp m WHERE e.mgrid=m.empid;