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


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:

self join example in sql server

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:


self join example in sql server

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:

self join example in sql server

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."
Previous
Next Post »

3 comments

Click here for comments
January 10, 2014 ×

can you explain that how we can insert the record in the primary key and foreign key table simultaneously. using c#

Reply
avatar
Ragu raman.M
admin
March 18, 2014 ×

Nice tutorial Sir.Hope to use the Self Join Query in my application.Really don't know this type of Query exists.Thanks a lot Sir.

Reply
avatar
March 19, 2014 ×

Hello Ragu raman..thanks for appreciating my work..it is always nice to hear that someone found my articles helpful..stay connected and keep reading for more useful updates..:)

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..