The SQL Self Join is one of the Join Type that is used to Join the 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.
SQL Self Join Syntax
The basic syntax of the Self Join in SQL Server 2014 is as shown below:
|
1 2 3 4 |
-- Syntax for SQL Server Self Join SELECT Tab1.Column(s), Tab2.Column(s), FROM Table1 AS Tab1, Table1 AS Tab2 |
NOTE: SQL ALIAS Table Name is mandatory for Self Join
In this article we will show you, How to write Self Join in SQL Server. For this, We are going to use Employees table present in our [SQL Server Tutorials] 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 Department and we have the Foreign key column DepartID.
SQL Self Join Example
In general, Every Department will have one Department Head and he will definitely belongs to one of the Employee. To get the Department Head name we have to use the Self Join using the Primary key and Foreign key. For example, The following SQL Query will display the Name of the Department Head for each and every Employee present in the Employees table.
T-SQL CODE
|
1 2 3 4 5 6 7 |
-- 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 |
OUTPUT
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.
Thank You for Visiting Our Blog
Good article and very east to understand, Thank you for posting.
Thank You
Good tutorial to learn SQL Server. Thank you so much.
Can you please post How to handle Dynamic SQL in SQL server if possible.
We will do as soon as possible