Databases Reference
In-Depth Information
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.
Pre-Oracle9
i
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.
EMPLOYEES
(Employee)
EMPLOYEES
(Manager)
