# Explanation of self-joins

## Problem

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

A simple example would be very helpful.

## 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
ABC   XYZ
DEF   ABC
XYZ   DEF
``````

## 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.

``````SELECT e.name, m.name
FROM employees e LEFT OUTER JOIN employees m
ON e.manager = m.id
``````

## 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.).

## 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
``````

## 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 e1.name, e2.name as ManagerName
from Employee e1, Employee e2 where
where e1.manager_id = e2.id
``````