Java Reference
In-Depth Information
A Self-Join is simply a normal SQL join that joins a table to itself. Use a Self-Join when rows in a table
contain references to other rows in the same table. Here's an example of this situation in a table of
employees, where each record contains a reference to the employee's supervisor by Employee_ID:
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
SUPERVISOR
100
Michael
Corleone
104
101
Fredo
Corleone
100
102
Sonny
Corleone
100
103
Francis
Corleone
100
104
Vito
Corleone
99
105
Tom
Hagen
100
106
Kay
Adams
100
107
Francis
Coppola
100
108
Mario
Puzo
100
Since the supervisor is also an employee, information about the supervisor is stored in the Employees
Table, so you use a Self-Join to access it. Do this by using table-name aliases to give each reference
to the table a separate name.
To get a list of employees and their supervisors, create a Self-Join by creating two separate references
to the Employees Table, using two different aliases. An example is shown in Figure 9-9 .
Figure 9-9: Using a Self-Join
The preceding SQL code is effectively creating what looks like two identical tables, e and boss, and
joining them using an Inner Join, so that you can get employee information from one reference to the
table and supervisor information from the other:
You can turn this into an Outer Self-Join very easily, as follows:
SELECT e.last_name, e.first_name,
Search WWH ::




Custom Search