What is SELF JOIN and when would you use it?

sql self-join
Imran picture Imran · Jul 29, 2010 · Viewed 440.9k times · Source

What is self join and when would you use it? I don't understand self joins so a layman explanation with an example would be great.

Answer

D'Arcy Rittich picture D'Arcy Rittich · Jul 29, 2010

You use a self join when a table references data in itself.

E.g., an Employee table may have a SupervisorID column that points to the employee that is the boss of the current employee.

To query the data and get information for both people in one row, you could self join like this:

select e1.EmployeeID, 
    e1.FirstName, 
    e1.LastName,
    e1.SupervisorID, 
    e2.FirstName as SupervisorFirstName, 
    e2.LastName as SupervisorLastName
from Employee e1
left outer join Employee e2 on e1.SupervisorID = e2.EmployeeID

Related questions

INNER JOIN same table

I am trying to get some rows from the same table. It's a user table: user has user_id and user_parent_id. I need to get the user_id row and user_parent_id row. I have coded something …

Read More
Simplest way to do a recursive self-join?

What is the simplest way of doing a recursive self-join in SQL Server? I have a table like this: PersonID | Initials | ParentID 1 CJ NULL 2 EB 1 3 MB 1 4 SW 2 5 YT NULL 6 IS 5 And I want to be able to get the records …

Read More
Explanation of self-joins

I don't understand the need for self-joins. Can someone please explain them to me? A simple example would be very helpful.

Read More