Self-Inflicted SQL

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

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:

and

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.

Now that we've finished working with joins for a while, what are your questions and comments about this aspect of MySQL?
You must be logged in to the O'Reilly Network to post a talkback.

Showing messages 1 through 9 of 9.

• 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

• 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)

• SQL 'self' Join
  2003-11-05 12:55:58  anonymous2 [Reply | View]
  
  
  Good explanation. Thanks!

• self join
  2003-03-14 22:57:44  anonymous2 [Reply | View]
  
  
  provides very good & clear explanation.

• Self Joins
  2002-09-10 05:43:36  anonymous2 [Reply | View]
  
  
  Very clear and useful explanation

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