Self-Joins - Oracle DBA

You now know that you can join tables to other tables, but can you join a table to itself, producing 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-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.

Pre-Oracle9i Self-Join Syntax

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

Pre-Oracle9i Self-Join Syntax

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.

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:

Oracle9i Self-Join Syntax

106 rows selected.

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


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle DBA Topics