What is the use of SELF JOIN in SQL SERVER with example?

ntroduction: In this article i am going to explain the use of self join in Sql Server using two examples. 

Description: A self join is basically a query in which a table is joined to itself and that is exactly why it is called a self join. So basically this join is always performed on single table as opposed to other joins that need more than one table.  Self-joins can be useful when we want to compare values in a column with other values in the same column in the same table.

To write the self join query we need to list a table twice in FROM clause and assign different alias to each instance of table  because tables participating in self join have same name, set up the comparison, and eliminatecases where a particular value would be equal to itself.

 

 

Syntax for SELF JOIN :

SELECT a.column_name, b.column_name...

FROM Table1 a, Table1 b

WHERE a.common_field = b.common_field;

Implementation: Let's understand the working of SELF JOIN using an two example. 

• First of all create a table for storing Employee details like Name, Address, City and managerId using the Script below.

CREATE TABLE [dbo].[EmDetails]

(

                [EmpId] [int] IDENTITY(1,1) NOT NULL,

                [Name] [varchar](50) NULL,

                [Address] [varchar](200) NULL,

                [City] [varchar](50) NULL,

                [ManagerId] [int] NULL

)

--Insert Sample data

INSERT INTO EmDetails (Name,Address,City,ManagerId) VALUES ('Lalit','#1234','Chandigarh',NULL)

INSERT INTO EmDetails (Name,Address,City,ManagerId) VALUES ('Raman','#4567','Panchkula',4)

INSERT INTO EmDetails (Name,Address,City,ManagerId) VALUES ('Arjun','#111','Chandigarh',1)

INSERT INTO EmDetails (Name,Address,City,ManagerId) VALUES ('Rohan','#321','Delhi',1)

INSERT INTO EmDetails (Name,Address,City,ManagerId) VALUES ('Raghav','#675','Noida',4)

INSERT INTO EmDetails (Name,Address,City,ManagerId) VALUES ('Sameer','#555','Panchkula',1)

INSERT INTO EmDetails (Name,Address,City,ManagerId) VALUES ('Kapil','#541','Chandigarh',1)

INSERT INTO EmDetails (Name,Address,City,ManagerId) VALUES ('Reshav','#909','Ambala',4)

This table will look like as show in image below:

Example 1:

Suppose our requirement is to find the employee name and their manager names. We need to write the following query:

SELECT e1.Name AS EmployeeName, e2.Name AS ManagerName

FROM EmDetails e1

INNER JOIN EmDetails e2

ON e1.ManagerId =e2.EmpId

ORDER BY e1.Name

• Output will be like as shown in image below:

Example 2 :

To find the employees living in the same city we can write the query using SELF JOIN as:

SELECT DISTINCTe1.Name,e1.Address,e1.City FROM dbo.EmDetails AS e1,dbo.EmDetails AS e2 WHERE e1.City=e2.City and e1.Name <> e2.Name ORDER BYe1.City,e1.Name

• Output will be like as shown in image below:

Notice that "Arjun", "Kapil" and "Lalit" live in the same city "Chandigarh" and Similarly "Raman" and "Sameer" live in the same city "Panchkula".