SQL Basics - Java Database Programming Bible - page 96

by creating two separate references to the Employees Table, using two different
aliases:

SELECT e.Last_Name, e.First_Name,
boss.Last_Name + ', ' + boss.First_Name AS Boss
FROM EMPLOYEES e, EMPLOYEES boss
WHERE e.supervisor = boss.employee_id

The preceding SQL code is effectively creating what looks like two identical tables, E
and Boss, and joining them using an Inner Join. This approach allows you to get the
employee information from one reference to the table and supervisor information from
the other, as shown here:

Last_Name First_Name Boss

Corleone Michael Corleone, Vito

Corleone Fredo Corleone, Michael

Corleone Sonny Corleone, Michael

Corleone Francis Corleone, Michael

Hagen Tom Corleone, Michael

Adams Kay Corleone, Michael

Coppola Francis Corleone, Michael

You can turn this into an Outer Self-Join very easily, as follows:

SELECT e.last_name, e.first_name,
boss.last_name + ', ' + boss.first_name AS Boss
FROM EMPLOYEES e, employees boss
WHERE e.supervisor *= boss.employee_id;

This returns one additional row, since the Employee_ID of Vito's supervisor does not
appear in the Employees Table. His boss appears as <NULL>, as shown here:

Last_Name First_Name Boss

Corleone Michael Corleone, Vito

Corleone Fredo Corleone, Michael

Corleone Sonny Corleone, Michael

Corleone Francis Corleone, Michael