SQL SELF JOIN

The SQL Self Join is used to Join Table 1 to Itself. If we have the Primary key and Foreign key in the same table, then we can use this Self join to connect them. The syntax of the Self Join in SQL Server is

-- Syntax for SQL Server Self Join
SELECT Tab1.Column(s), Tab2.Column(s),
FROM Table1 AS Tab1, 
     Table1 AS Tab2

For this Self Join in SQL Server demo, we use the Employees table present in our SQL Server Database. As you can observe, it has 15 different employees, and each person belongs to the Department. We have the Foreign key column DepartID.

SQL Self Join Example

In general, Every Department will have one Department Head, and he belongs to one of the employees. To get the Department Head name, we have to use the Self Join using the Primary key & Foreign key. For example, The following Self Join Query will display the Department Head’s Name for each Employee present in the Employees table.

NOTE: ALIAS Table Name is mandatory for Self Join.

-- SQL Server Self Join Example
SELECT EMP1.[FirstName]
      ,EMP1.[LastName]
   ,EMP1.[Department Name]
   ,EMP2.[FirstName] +' '+ EMP2.[LastName] AS [Department Head]
  FROM [Employees] AS EMP1, [Employees] AS EMP2
  WHERE EMP1.DepartID = EMP2.ID

From the below SQL Server screenshot, Rob Walter is not only Sr. Software Developer but also Department Head for the remaining Sr. Software Developer in an organization.

Use this Self Join when you have a Primary key and Foreign key in a single table.