1. Technology
Send to a Friend via Email
You can opt-out at any time. Please refer to our privacy policy for contact information.

Discuss in my forum

Self-Joins in SQL

Using Self-Joins to Access Related Records in the Same Table


Black businessman using laptop at conference table
Ariel Skelley/Blend Images/Getty Images
You can use a self-join to simplify nested SQL queries where the inner and outer queries reference the same table. These joins allow you to retrieve related records from the same table. The most common case where you'd use a self-join is when you have a table that references itself, such as the employees table shown below:
id first_name last_name manager
----------- --------------- --------------- -----------
1 Pat Crystal NULL
2 Dennis Miller 1
3 Jacob Smith 1
4 Allen Hunter 2
5 Mary Underwood 3
6 Joy Needham 3
In this table, the manager attribute simply references the employee ID of another employee in the same table. For example, Dennis Miller reports to Pat Crystal. Pat is apparently the president of this company, as she reports to no one.

Suppose you're tasked with writing a SQL query to retrieve a list of employees and their managers. You can't write a basic SQL SELECT statement to retrieve this information, as you need to cross reference information contained in other records within the same table. Fortunately, you can use a self-join to solve this dilemma by joining the table to itself.

Here's the SQL statement that will retrieve the desired results:
SELECT e.first_name AS 'Employee FN', e.last_name AS 'Employee LN', m.first_name AS 'Manager FN', m.last_name AS 'Manager LN'
FROM employees AS e LEFT OUTER JOIN employees AS m
ON e.manager =m.id
And the corresponding output:
Employee FN Employee LN Manager FN Manager LN
--------------- --------------- --------------- ---------------
Pat Crystal NULL NULL
Dennis Miller Pat Crystal
Jacob Smith Pat Crystal
Allen Hunter Dennis Miller
Mary Underwood Jacob Smith
Joy Needham Jacob Smith
(6 row(s) affected)
Notice that it's extremely important to select the correct join type when writing a self-join. In this case, we used a LEFT OUTER JOIN to ensure we had output records corresponding to each employee. If we used an INNER JOIN instead, we would have omitted Pat Crystal, the company president, from our list, as she does not have a manager.
  1. About.com
  2. Technology
  3. Databases
  4. Learning SQL
  5. Advanced SQL Topics
  6. Self-Joins in SQL Statements

©2014 About.com. All rights reserved.