Self-Inflicted SQL
07/12/2001Ahh ... the joys of SQL joins. We've talked about a number of different kinds of joins over the past few columns, and now we'll finally wind up with the mysterious "self join."
This will lead naturally into the next column's topic -- SQL subqueries. After that, we'll head back to set theory and the UNION statement to wrap up our toolbox of ways to create record sets from the tables in the database. Further up the road, we'll move into the SQL statements for creating and managing the database, and tables themselves which should occupy us for quite a while.
So what's a self-join?
A self-join is one of those tools that is only useful when you're stuck in a particular type of situation -- specifically when you have a normalized database table that needs to be "flattened." The typical situation is when a table of data contains a link to data values that are stored in the same table.
A standard example you'll see in SQL books is an "Employees" table that contains a field for the "Manager ID", which is a link to one of the records in the Employees table, as seen in the following example.
|
Employees
|
||
| EmployeeID | EmployeeName | ManagerID |
| 61 | Sue Smith | (null) |
| 62 | David Jones | 61 |
| 63 | Troy Parker | 61 |
| 64 | Claire Smith-Jones | 63 |
| 65 | Grover Rivers | 63 |
|
Also in aboutSQL: |
How would you create a query that will return the names of the manager of each employee? One way would be to nest queries together in a loop -- a common approach for web developers (PHP, ASP, ColdFusion, JSP, etc.) faced with this problem. That's an awful solution from a performance perspective. A SQL joinis much faster than doing a lot of combinations of separate queries using server-side technology. We'll use a self-join instead.
Using self-joins
A self-join is simply a normal SQL join that joins one table to itself. This is accomplished by using table name aliases (which were discussed several columns ago) to give each "instance" of the table a separate name. For example:
SELECT E1.EmployeeName AS Employee, E2.EmployeeName AS Manager FROM Employees AS E1 INNER JOIN Employees AS E2 ON E1.ManagerID = E2.EmployeeID
What we're doing with the SQL code above is effectively creating two identical tables:
|
E1 (Employees)
|
||
| EmployeeID | EmployeeName | ManagerID |
| 61 | Sue Smith | (null) |
| 62 | David Jones | 61 |
| 63 | Troy Parker | 61 |
| 64 | Claire Smith-Jones | 63 |
| 65 | Grover Rivers | 63 |
and
|
E2 (Employees)
|
||
| EmployeeID | EmployeeName | ManagerID |
| 61 | Sue Smith | (null) |
| 62 | David Jones | 61 |
| 63 | Troy Parker | 61 |
| 64 | Claire Smith-Jones | 63 |
| 65 | Grover Rivers | 63 |
These tables are joined on the highlighted columns. Note that you are not truly creating another copy of the table -- you are joining the table to itself, but the effect is easier to understand if you think about it as two tables (at least it is for me!).
Next steps
Whew! That's it for SQL joins, at least for now. There are some more esoteric ways to join tables together but we've got a lot more common topics to cover first -- particularly the idea of subqueries which we discussed here. Until then, feel free to contact me with your comments and questions.
John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.
Read more aboutSQL columns.
Return to ONLamp.com.
You must be logged in to the O'Reilly Network to post a talkback.
Showing messages 1 through 13 of 13.
-
a little diffferent self join
2008-06-13 00:04:39 adexfe [Reply | View]
check out a little advance self join at http://weblog.xanga.com/adexfe/661183711/comparing-columns-in-the-same-table-self-join.html ()
-
recursive sql
2008-06-09 09:59:23 krmsiva [Reply | View]
For example, the company has employiees and managers and their reporting managers also there. Find out if given employee name or id based on that retrieve the immediate manager and who is reporting to another manager
example : emp siva is reported to kumar but kumar is reported to two managers likewise
-
sql
2006-03-10 04:10:10 mmmmm1 [Reply | View]
plz reply the query that if we want to compare one column with in the same table with using update command.
(update categorydet e1,categorydet e2 set e1.cateid=1 where e1.cateid=e2.cateid)
is it correct or not .plz reply immidiately.
thanks -
Find Child
2007-12-17 10:41:37 nv_thien [Reply | View]
EmployeeID EmployeeName ManagerID
61 Sue Smith (null)
62 David Jones 61
63 Troy Parker 62
64 Claire 63
65 Grover Rivers 64
66 Grover Rivers 62
67 Grover Rivers 63
can you help me.
i want to make procedure and pass @EmployeeID
it'll return all childs
*** for example 1 : GetChild 62
it'll return to
EmployeeID EmployeeName ManagerID
63 Troy Parker 62
64 Claire 63
65 Grover Rivers 64
66 Grover Rivers 62
67 Grover Rivers 63
*** for example 2 : GetChild 63
it'll return to
EmployeeID EmployeeName ManagerID
64 Claire 63
65 Grover Rivers 64
67 Grover Rivers 63
-
update on self joins
2006-02-10 10:32:01 msprotools [Reply | View]
when i do a select on self join its really doing wonders, however, when i do an update, is says ambiguous table name.
UPDATE NODE_DEFN
Set [Master Account] = ndf2.[Master Account],
[Account]= n1.NDF_NUMBER
FROM NODE_DEFN as n1
left outer join NODE_DEFN ndf2
on n1.NDF_PARENT_RID = ndf2.NDF_RID
WHERE n1.ndf_ABSLEVEL = 1
-
Summing up data from a table
2004-10-19 03:55:35 Sona [Reply | View]
I have 2 integer columns and want to sum up the commission with same trackId and insert it into another column of same table.But with Sum()function, I don't get as many number of sums as the number of rows in the table. My hard coded query looks like:
UPDATE Temp_GetPercentage
SET relPercentage = (SELECT sum(per.intImpressions)
FROM Temp_GetPercentage as per WHERE track_id = 20)
This should update all records with track_id = 20 and set relPercentage = (sum of intImpressions having track_id = 20)
-
self join
2002-05-11 18:45:20 laur10rose [Reply | View]
I typed in the sample join exactly as you stated into Mysql, but it doesn't work. Is there another method to doing a self-join? I need to do exactly what you described for a bulletin board application. I have the parentid field corresponding to the id field in the same table.
Any assistance would be appreciated. Thanks.
