sqlself-join

Explanation of self-joins

What are self-joins?

What is a simple example?

Solution

  • 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