Problem
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
ABC XYZ
DEF ABC
XYZ DEF
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.
SELECT e.name, m.name
FROM employees e LEFT OUTER JOIN employees m
ON e.manager = m.id
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 e1.name, e2.name as ManagerName
from Employee e1, Employee e2 where
where e1.manager_id = e2.id
Answered by Klaus Byskov Pedersen
Post is based on https://stackoverflow.com/questions/2458519/explanation-of-self-joins