CODEWITHSUNDEEP
sqlself-join
Imran
Imran

Reputation: 11654

What is SELF JOIN and when would you use it?

What is SELF JOIN and when would you use it?

Upvotes: 151

Views: 541306

Answers (5)

paxdiablo
paxdiablo

Reputation: 882326

Well, one classic example is where you wanted to get a list of employees and their immediate managers. Since managers are also employees, they exist in the same table:

select      e.id as empl_id,
            e.name as empl_name,
            m.id as mgr_id,
            m.name as mgr_name
  from      emptable e,
            emptable m
 where      e.manager_id = m.id

It's basically used where there is any relationship between rows stored in the same table. For example:

  • employees, already discussed.
  • multi-level marketing where marketers have a relationship with their upstream people, however dubious the relationship may be :-)
  • machine parts where, for example, a bicycle front wheel "part" is built up from (at least) rim, spoke, tyre, and axle parts.

There are other examples, those are simply the ones that came to mind quickly.

Upvotes: 85

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171539

You use a self join when a table references data in itself.

E.g., an Employee table may have a SupervisorID column that points to the employee that is the boss of the current employee.

To query the data and get information for both people in one row, you could self join like this:

select e1.EmployeeID, 
    e1.FirstName, 
    e1.LastName,
    e1.SupervisorID, 
    e2.FirstName as SupervisorFirstName, 
    e2.LastName as SupervisorLastName
from Employee e1
left outer join Employee e2 on e1.SupervisorID = e2.EmployeeID

Upvotes: 213

Mark Byers
Mark Byers

Reputation: 838974

A self join is simply when you join a table with itself. There is no SELF JOIN keyword, you just write an ordinary join where both tables involved in the join are the same table. One thing to notice is that when you are self joining it is necessary to use an alias for the table otherwise the table name would be ambiguous.

It is useful when you want to correlate pairs of rows from the same table, for example a parent - child relationship. The following query returns the names of all immediate subcategories of the category 'Kitchen'.

SELECT T2.name
FROM category T1
JOIN category T2
ON T2.parent = T1.id
WHERE T1.name = 'Kitchen'

Upvotes: 27

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

SQL self-join simply is a normal join which is used to join a table to itself.

Example:

Select *
FROM Table t1, Table t2
WHERE t1.Id = t2.ID

Upvotes: 11

Will A
Will A

Reputation: 25008

You'd use a self-join on a table that "refers" to itself - e.g. a table of employees where managerid is a foreign-key to employeeid on that same table.

Example:

SELECT E.name, ME.name AS manager
FROM dbo.Employees E
LEFT JOIN dbo.Employees ME
ON ME.employeeid = E.managerid

Upvotes: 6

Related Questions