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:
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,