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,
insert into Employee values('B001','michael ','jackson','firstname.lastname@example.org',2,2)
insert into Employee values('B002','johan','Kumar','email@example.com',1,1)
insert into Employee values('B003','james ','bond','firstname.lastname@example.org',1,2)
insert into Employee values('B004','Vivek','Kumar','email@example.com',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
Emp1.EmpFirstName+' '+Emp1.EmpLastName as EmployeeName,
Emp2.EmpFirstName+' '+Emp2.EmpLastName as ManagerName
FROM Employee Emp1
INNER JOIN Employee Emp2
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.