Self-Joins in SQL

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.