Examples of Self JOIN

Hello friends,

Self Join is very basic thing in SQL.I am explaining it with example in SQL Server
Enjoy Self JOIN

Step 1 :Create a table named as employee Run the following Query


CREATE TABLE [dbo].[employee](
[empId] [int] NOT NULL,
[empname] [nvarchar](1200) NOT NULL,
[managerid] [int] NOT NULL
) ON [PRIMARY]

Step 2 : Insert data into it

Insert into employee values (1,'john',1)
Insert into employee values (2,'Jasmine',1)
Insert into employee values (3,'Rahman',2)
Insert into employee values (4,'Victor',3)
Insert into employee values (5,'Majid',1)
Insert into employee values (6,'Olvin',3)
Insert into employee values (7,'Soniya',2)

step 3 : Now ,I want the records of employees with thier manager Id and manager name
so I will apply Self JOIN on employee table on empId and managerId.

Check and run this Query

SELECT a.empid,a.empname as EmployeeName,a.managerid as ManagerId,b.empName as ManagerName FROM
employee a INNER JOIN employee b
ON b.empid=a.managerid

Output will seen like as
EmpId empname managerid ManagerName

1 john 1 john
2 Jasmine 1 john
3 Rahman 2 Jasmine
4 Victor 3 Rahman
5 Majid 1 john
6 Olvin 3 Rahman
7 Soniya 2 Jasmine

Any Suggestions will be appreciated ! :)

Thanks
Shubahnk Upadhyay