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

Introduction: In this article i am going to explain the use of self join in Sql Server using two examples. 
In previous articles i explained FULL OUTER JOIN in SQL SERVER with example and CROSS JOIN or CARTESIAN JOIN and Use of Cursor in SQL SERVER with simple example and Transaction in SQL Server and ACID properties and Downgrade SQL Server 2012,2008 database to SQL Server 2005 or lower version and Split large string separated by comma 

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 eliminate cases 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 DISTINCT e1.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 BY e1.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".

Now over to you:

" I hope you have got what is SELF JOIN in SQL SERVER using the example above and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."

• Tweet
• Share
• Share
• Share

Related Post