SQL SELF JOIN

The SQL Server Self 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 SQL Self Join one to connect them, and the syntax is shown below.

SELECT Tab1.Column(s), Tab2.Column(s),
FROM Table1 AS Tab1, 
     Table1 AS Tab2

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

Employee Table 1

SQL Server Self Join Example

In general, Every Department will have one 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 & Foreign key. For example, The following SQL Self Join query will display the Head’s Name for each person present in the Employees table.

NOTE: ALIAS Name is mandatory for this one.

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 Server screenshot, Rob Walter is not only Sr. Software Developer but also Department Head for the remaining Sr. Software Developer in the organization. Use this one when you have a Primary and Foreign in a single table.

SQL SELF JOIN Table to itself 2

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.

Comments are closed.