What is Self join in SQL?

Question

Can anyone explain Self join and when to use self join in SQL?

Answer 1

A self-join is just like normal join but the table is joined with itself. Here is a classic example when to use self join. For example, you have an Employee table with Employee_ID and Supervisor_ID that refers to the boss of that employee. If you want to find out the list of employees and their immediate managers.

SELECT e1.Employee_ID, 

              e1.First_Name,

              e1.Last_Name,

              e1.Supervisor_ID,

              e2.First_Name as Supervisor_FirstName,

              e2.Last_Name as Supervisor_LastName

FROM Employee e1

LEFT OUTER JOIN Employee e2 on e1.Supervisor_ID = e2.Employee_ID

I suggest registering for this SQL course by Intellipaat to learn SQL.