Coder Perfect

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.

Asked by Alex Gordon

Solution #1

Self-join can be viewed as two identical tables. However, because you can’t make two copies of the table during normalization, you have to rely on self-join to simulate having two tables.

Let’s say you’ve got two tables:

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

Now, if you want to receive each employee’s name along with the names of his or her bosses, follow these steps:

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

The following table will be generated as a result of this command:

Name  Boss

Answered by pointlesspolitics

Solution #2

When you have a table that references itself, it’s pretty common. An employee table, for example, where each employee may have a manager, and you want to list all employees along with their boss’s name.

FROM employees e LEFT OUTER JOIN employees m
ON e.manager =

Answered by windyjonas

Solution #3

A self join is when a table is joined to itself.

When the table stores entities (records) that have a hierarchical relationship between them, this is a common use case. For instance, a table holding personal information (Name, DOB, Address…) with a column showing the Father’s (and/or Mother’s) ID. Then, with a simple query such as

SELECT Child.ID, Child.Name, Child.PhoneNumber, Father.Name, Father.PhoneNumber
FROM myTableOfPersons As Child
LEFT OUTER JOIN  myTableOfPersons As Father ON Child.FatherId = Father.ID
WHERE Child.City = 'Chicago'  -- Or some other condition or none

In the same query, we may receive information about both the child and the father (and the mother, with a second self join, and even grand parents, etc.).

Answered by mjv

Solution #4

Assume you have a table of users set up as follows:

If you wanted to get both the user’s and the manager’s information in one query in this case, you could do so as follows:

SELECT users.user_id, users.user_name, managers.user_id AS manager_id, managers.user_name AS manager_name INNER JOIN users AS manager ON users.manager_id=manager.user_id

Answered by ceejayoz

Solution #5

Assume you have a table called Employee, as seen below. Every employee has a manager who is likewise an employee (maybe with the exception of the CEO, whose manager id is null).

Table (Employee): 

int id,
varchar name,
int manager_id

Then, to discover all employees and their bosses, use the following select:

select, as ManagerName
from Employee e1, Employee e2 where
where e1.manager_id =

Answered by Klaus Byskov Pedersen

Post is based on