What is SELF JOIN and when would you use it?

Question

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.

D'Arcy Rittich · Accepted Answer · 2010-07-29T18:59:19.373

203

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

edited Jul 29 '10 at 18:59

answered Jul 29 '10 at 11:37

D'Arcy Rittich

167,292
40
290
283

1

That looks like a self outer join to me? – Joe Caruso Dec 09 '16 at 02:47
7

@JoeCaruso That's because it is ;) – D'Arcy Rittich Dec 09 '16 at 18:26
7

I wonder why noone emphasizes the "in the same row" part. Isn't that whole point of doing a self join? – A. Sallai Jan 02 '17 at 10:36
1

don't we need `AS` when creating alias for tables? – Manu Chadha Jan 08 '19 at 07:13
3

@ManuChadha `AS` is optional - I will decide based on the readability of the query. – D'Arcy Rittich Jan 08 '19 at 12:58
@JoeCaruso LEFT JOIN and LEFT OUTER JOIN are the same thing – mjeshtri Apr 27 '22 at 16:24

score 84 · Answer 2 · answered Jul 29 '10 at 11:39

Well, one classic example is where you wanted to get a list of employees and their immediate managers:

select e.employee as employee, b.employee as boss
from emptable e, emptable b
where e.manager_id = b.empolyee_id
order by 1

It's basically used where there is any relationship between rows stored in the same table.

employees.
multi-level marketing.
machine parts.

And so on...

Mark Byers · Answer 3 · 2010-07-29T11:48:45.807

26

A self join is simply when you join a table with itself. There is no SELF JOIN keyword, you just write an ordinary join where both tables involved in the join are the same table. One thing to notice is that when you are self joining it is necessary to use an alias for the table otherwise the table name would be ambiguous.

It is useful when you want to correlate pairs of rows from the same table, for example a parent - child relationship. The following query returns the names of all immediate subcategories of the category 'Kitchen'.

SELECT T2.name
FROM category T1
JOIN category T2
ON T2.parent = T1.id
WHERE T1.name = 'Kitchen'

edited Jul 29 '10 at 11:48

answered Jul 29 '10 at 11:38

Mark Byers

811,555
193
1,581
1,452

Nice to see this comment.. ON T2.parent = T1.id (because it affects result if we interchange 'parent' and 'id' in it) – himanshupareek66 Feb 19 '17 at 16:25
5

Good to mention there is no `SELF JOIN` keyword. I was confused about that part! – nclsvh Feb 12 '18 at 15:03

score 11 · Answer 4 · answered Jul 29 '10 at 11:38

11

SQL self-join simply is a normal join which is used to join a table to itself.

Example:

Select *
FROM Table t1, Table t2
WHERE t1.Id = t2.ID

answered Jul 29 '10 at 11:38

Michael Pakhantsov

24,855
6
60
59

21

Wouldn't it be safer to use `SELECT t1.*` in order to avoid duplicate column names in the result? – Matthieu Sep 28 '13 at 14:08

score 6 · Answer 5 · answered Jul 29 '10 at 11:38

6

You'd use a self-join on a table that "refers" to itself - e.g. a table of employees where managerid is a foreign-key to employeeid on that same table.

Example:

SELECT E.name, ME.name AS manager
FROM dbo.Employees E
LEFT JOIN dbo.Employees ME
ON ME.employeeid = E.managerid

answered Jul 29 '10 at 11:38

Will A

24,780
5
50
61

ON ME.employeeid = E.managerid will create different result instead of ON ME.managerid = E.employeeid – himanshupareek66 Feb 19 '17 at 16:24

What is SELF JOIN and when would you use it?

5 Answers5

Linked