Friday, June 11, 2010

Self Join in SQL Server 2008

In a Self Join, a table is joined with itself. This type of join is called Self join. As a result, one row in a table correlates with other rows in the same table. In a Self join, a table name is used twice in the query. We use the table aliases to make identical copies of the same table to be open in different memory locations. 

Query of Self Join.

CREATE TABLE [dbo]. [Employee]
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLastName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Managerid] [int] NULL,
[Departmentid] [INT]

insert into Employee values('B001','michael ','jackson','',2,2)
insert into Employee values('B002','johan','Kumar','',1,1)
insert into Employee values('B003','james ','bond','',1,2)
insert into Employee values('B004','Vivek','Kumar','',1,NULL)

CREATE TABLE [dbo].[Department]
[Departmenttid] [int] IDENTITY (1, 1) NOT NULL primary key,
[DepartmentName] [nvarchar](255) NOT NULL

select *from Department
insert into Department values('Accounts')
insert into Department values('Admin')
insert into Department values('HR')
insert into Department values('Technology')

Command in Self Join  

SELECT Emp1.Empid,
       Emp1.EmpFirstName+' '+Emp1.EmpLastName as EmployeeName,
  Emp2.EmpFirstName+' '+Emp2.EmpLastName as ManagerName
  FROM Employee Emp1
     INNER JOIN Employee Emp2
  ON Emp1.Managerid=Emp2.Empid

The result is displayed below with Self Join the both table Employee and Department.

NOTE:-Since the employee and the manager information is contained in the same table (Employee, since both are employees), we have to use the Self Join. In the self join query, we make two copies of the table Employee by using the aliases Emp1 and Emp2 and then use Inner join between them by using the managerid column of the Emp1 and Empid column of the table Emp2.In this example, we use managerid and empid columns of the Employee table since the employee id of the manager of an employee is stored in the managerid of the Employee table.


Anonymous said...


Tina Kenard said...

Excellent blog and it’s totally loaded with valid posts on Java and .Net technology. Consider including RSS feed in your blog, so aspirants like me can follow your blog easily. .Net Training in Chennai|Best DOT NET Training institute in Chennai

Recent Posts