How to use Self Join to retrieve Supervisor Name based on Supervisor Id from Employee Table in SQL Server

SQL Server Self Join to retrieve Supervisor Name based on Supervisor Id from same Employee Table

Joining the table to itself is called Self Join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. 

Here we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.

Based on Scenario, a Self Join could be an Outer Join or an Inner Join.

Scenario :

Suppose we have an Employee Table with Employee and Supervisor Id details. Here, based on the Supervisor Id we need to generate a Supervisor Name from the same Table.

SELECT [EmpId],[EmpName],[JobTitle],[SupervisorId] FROM [dbo].[Tbl_Emp]

GO

We can generate a Supervisor Name from the same table by using the Self Join as follows..

A) Self Join
Select   T1.[EmpId],T1.[EmpName]

            ,T1.[JobTitle],T1.[SupervisorId]
            ,T2.[EmpName] as [SupervisorName]
            ,T2.[JobTitle] as [SuperJobTitle]
From   Tbl_Emp T1 , Tbl_Emp T2
Where T1.SupervisorId=T2.EmpId

B) Self Join using Inner Join

Select T1.[EmpId],T1.[EmpName]
          ,T1.[JobTitle],T1.[SupervisorId]
          ,T2.[EmpName] as [SupervisorName]
          ,T2.[JobTitle] as [SuperJobTitle]
From Tbl_Emp T1 Inner Join Tbl_Emp T2
         On T1.SupervisorId=T2.EmpId

Result :

C) Self Join using Left  Outer Join

Select T1.[EmpId],T1.[EmpName]
          ,T1.[JobTitle],T1.[SupervisorId]
          ,T2.[EmpName] as [SupervisorName]
          ,T2.[JobTitle] as [SuperJobTitle]
From Tbl_Emp T1 Left Join Tbl_Emp T2
         On T1.SupervisorId=T2.EmpId

Result :

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------