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.
The query that Janice writes displays the employees who have managers:
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:
106 rows selected.
Not unexpectedly, she gets the same results as she did with the pre-Oracle9i version of the query.
Oracle DBA Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Oracle 11g Interview Questions|
|Oracle apps Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle ADF Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps Functional Interview Questions|
|Oracle Apps DBA Interview Questions||Oracle Workflow Interview Questions|
Oracle Dba Tutorial
Relational Database Concepts
Sql*plus And Isql*plus Basics
Oracle Database Functions
Restricting, Sorting, And Grouping Data
Using Multiple Tables
Advanced Sql Queries
Installing Oracle And Creating A Database
Creating And Maintaining Database Objects
Users And Security
Making Things Run Fast (enough)
Saving Your Stuff (backups)
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.