self join query in sql server | self join example in sql server

Introduction:

Here I will explain how to write self join query in SQL server and self join example in SQL server.

Description:

In previous article I explained about Joins in SQL Server and many more articles relating to SQL Server. Now I will explain about self join in SQL Server.

Self Join

Joining the table itself called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. Here we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.

Syntax for self join

SELECT e1.EmpId, e1.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;

Here I will explain self join with one example for that first design one table and give name as EmployeeDetails in your database as shown below

Column Name	Data Type	Allow Nulls
EmpId	Int (set Identity=true)	No
EmpName	varchar(50)	Yes
ManagerId	Int	Yes

Once table designed please enter the data in your table that as shown below.

EmpId	EmpName	ManagerId
1	Suresh	0
2	Prasanthi	1
3	Mahesh	1
4	Sai	2
5	Madhav	2
6	Honey	5

Now if I want get the details of Empolyees who are in Manager Position for that we need to write query like this

SELECT DISTINCT e1.EmpId, e1.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;

If I run above query we will get records like this

EmpId	EmpName
1	Suresh
2	Prasanthi
5	Madhav

Suppose if I want get the details of Empolyees who are having Managers then we need to write query like

SELECT e2.EmpId, e2.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;

If I run above query we will get records like this

EmpId	EmpName
2	Prasanthi
3	Mahesh
4	Sai
5	Madhav
6	Honey

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email