sql: self-joins explained

Question

i dont understand the need for self-joins. can someone please explain them to me? a simple example would be very helpful

Answer 1

Let's say you have a table users, set up like so:

• user ID
• user name
• user's manager's ID

In this situation, if you wanted to pull out both the user's information and the manager's information in one query, you might do this:

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

Answer 2

It's quite common when you have a table that references itself. Example: an employee table where every employee can have a manager, and you want to list all employees and the name of their manager.

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

Answer 3

They are useful if your table is self-referential. For example, for a table of pages, each page may have a next and previous link. These would be the IDs of other pages in the same table. If at some point you want to get a triple of successive pages, you'd do two self-joins on the next and previous columns with the same table's id column.

Answer 4

Imagine a table called Employee as described below. All employees have a manager which is also an employee (maybe except for the CEO, whose manager_id would be null)

Table (Employee): 

int id,
varchar name,
int manager_id

You could then use the following select to find all employees and their managers:

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

Answer 5

A self join is a join of a table with itself.

A common use case is when the table stores entities (records) which have a hierarchical relationship between them. For example a table containing person information (Name, DOB, Address...) and including a column where the ID of the Father (and/or of the mother) is included. Then with a small query like

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

we can get info about both child and father (and mother, with a second self join etc. and even grand parents etc...) in the same query.

Answer 6

It's the database equivalent of a linked list/tree, where a row contains a reference in some capacity to another row.

Answer 7

Without the ability for a table to reference itself, we'd have to create as many tables for hierarchy levels as the number of layers in the hierarchy. But since that functionality is available, you join the table to itself and sql treats it as two separate tables, so everything is stored nicely in one place.

Answer 8

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 get the name of each employee with Boss names

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

Answer 9

Hello all , Can any one please give me the query to get the child name and his grand fathers name For eg - if i have a table Relationships in the i have childid and fatherid columns so how will i get the grandfather,

i can easily get the father name by using a join but for grandfather i need to do joins 2 times so can any one help me with this

D.Mahesh