The basic 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
NOTE: ALIAS Table Name is mandatory for Self Join
For this Self Join in SQL Server demo, we are going to use Employees table present in our SQL Server Database. Data present in the Employees Table is:
From the above screenshot, you can observe that we have 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 will belong to one of the Employee. 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 Name of the Department Head for each Employee present in the Employees table.
-- 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
If you observe the above screenshot, Rob Walter is not only Sr. Software Developer but also Department Head for the remaining Sr. Software Developer in an organization.