5.4. Self-Joins

You now know that you can join tables to other tables, but can you join a table to itself, producing a self-join? The answer is a resounding, but qualified, yes. Typically, a table will join to itself when the table is designed in a hierarchical manner, that is, when one particular row in a table is somehow related to another row in the table in a parent-child relationship.

self-join

A join of a table to itself where a non-primary key column in the table is related to the primary key column of another row in the same table.

At Scott's widget company, the EMPLOYEES table has a column that contains the employee number of the employee (EMPLOYEE_ID) in addition to a column that contains the employee number of the employee's immediate supervisor (MANAGER_ID). Janice will use this information to produce some new reports for the boss that essentially join the EMPLOYEES table to itself.

hierarchical A table design where one of the foreign keys in the table references the primary key of the same table in a parent-child relationship.

5.4.1. Pre-Oracle9i Self-Join Syntax

Since the EMPLOYEES table contains the employee's manager number, Janice decides to become proactive and generate a report of all employees and their managers. Her SELECT query references the EMPLOYEES table twice: once as an EMPLOYEES table and once as a MANAGERS table, since all of the managers are employees themselves. The EMPLOYEES table can be related to itself.

The query that Janice writes displays the employees who have managers:

select e.employee_id "Emp ID", e.last_name "Emp Name",
       m.employee_id "Mgr ID", m.last_name "Mgr Name"
from employees e, employees m
where e.manager_id = m.employee_id;

    Emp ID Emp Name            Mgr ID Mgr Name
---------- --------------- ---------- ---------------
       201 Hartstein              100 King
       149 Zlotkey                100 King
       148 Cambrault              100 King
...
       177 Livingston             149 Zlotkey
       176 Taylor                 149 Zlotkey
       175 Hutton                 149 Zlotkey
       174 Abel                   149 Zlotkey
       202 Fay                    201 Hartstein
       206 Gietz                  205 Higgins

106 rows selected.

Notice that King is not in the list. Since the row in the EMPLOYEES table for King does not have an entry for a manager (he has no manager since he is the president of the company), his row does not match any rows in the other copy of the EMPLOYEES table and therefore does not show up as a row in the query output.

5.4.2. Oracle9i Self-Join Syntax

The Oracle9i syntax not only moves the join condition to the FROM clause, it also uses the familiar syntax you saw earlier for joining two different tables—the JOIN ... ON syntax. Janice rewrites the manager query using the Oracle9i syntax as follows:

select e.employee_id "Emp ID", e.last_name "Emp Name",
       m.employee_id "Mgr ID", m.last_name "Mgr Name"
from employees e
      join employees m
      on e.manager_id = m.employee_id;

     Emp ID Emp Name            Mgr ID Mgr Name
 ---------- --------------- ---------- ---------------
        201 Hartstein              100 King
        149 Zlotkey                100 King

148 Cambrault              100 King
...
        177 Livingston             149 Zlotkey
        176 Taylor                 149 Zlotkey
        175 Hutton                 149 Zlotkey
        174 Abel                   149 Zlotkey
        202 Fay                    201 Hartstein
        206 Gietz                  205 Higgins

106 rows selected.

Not unexpectedly, she gets the same results as she did with the pre-Oracle9i version of the query.