Explanation of self-joins

sql self-join
Alex Gordon picture Alex Gordon · Mar 16, 2010 · Viewed 36.7k times · Source

I don't understand the need for self-joins. Can someone please explain them to me?

A simple example would be very helpful.

Answer

pointlesspolitics picture pointlesspolitics · Mar 16, 2010

You can view self-join as two identical tables. But in normalization, you cannot create two copies of the table so you just simulate having two tables with self-join.

Suppose you have two tables:

Table emp1

Id Name Boss_id            
1   ABC   3                   
2   DEF   1                   
3   XYZ   2

Table emp2

Id Name Boss_id            
1   ABC   3                   
2   DEF   1                   
3   XYZ   2

Now, if you want to get the name of each employee with his or her boss' names:

select c1.Name , c2.Name As Boss
from emp1 c1
    inner join emp2 c2 on c1.Boss_id = c2.Id

Which will output the following table:

Name  Boss
ABC   XYZ
DEF   ABC
XYZ   DEF

Related questions

What is SELF JOIN and when would you use it?

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.

Read More
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