Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-01 |
|
SQL Queries and Subqueries, 4 of 11
If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause:
hierarchical_query_clause::=
START
WITH
specifies the root row(s) of the hierarchy.CONNECT
BY
specifies the relationship between parent rows and child rows of the hierarchy. In a hierarchical query, one expression in condition
must be qualified with the PRIOR
operator to refer to the parent row. For example,
... PRIOR expr = expr
or
... expr = PRIOR expr
If the CONNECT
BY
condition
is compound, then only one condition requires the PRIOR
operator. For example:
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id
In addition, the CONNECT
BY
condition
cannot contain a subquery.
PRIOR
is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
PRIOR
is most commonly used when comparing column values with the equality operator. (The PRIOR
keyword can be on either side of the operator.) PRIOR
causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT
BY
clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error.
The manner in which Oracle processes a WHERE
clause (if any) in a hierarchical query depends on whether the WHERE
clause contains a join:
WHERE
predicate contains a join, Oracle applies the join predicates before doing the CONNECT
BY
processing.WHERE
clause does not contain a join, Oracle applies all predicates other than the CONNECT
BY
predicates after doing the CONNECT
BY
processing without affecting the other rows of the hierarchy.Oracle uses the information from the hierarchical query clause to form the hierarchy using the following steps:
WHERE
clause either before or after the CONNECT
BY
clause depending on whether the WHERE
clause contains any join predicates (as described in the preceding bullet list).START
WITH
condition.CONNECT
BY
condition with respect to one of the root rows.CONNECT
BY
condition with respect to a current parent row.WHERE
clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE
clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.To find the children of a parent row, Oracle evaluates the PRIOR
expression of the CONNECT
BY
condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT
BY
condition can contain other conditions to further filter the rows selected by the query. The CONNECT
BY
condition cannot contain a subquery.
If the CONNECT
BY
condition results in a loop in the hierarchy, then Oracle returns an error. A loop occurs if one row is both the parent (or grandparent or direct ancestor) and a child (or a grandchild or a direct descendent) of another row.
Note: In a hierarchical query, do not specify either |
The following hierarchical query uses the CONNECT
BY
clause to define the relationship between employees and managers:
SELECT employee_id, last_name, manager_id FROM employees CONNECT BY PRIOR employee_id = manager_id; EMPLOYEE_ID LAST_NAME MANAGER_ID ----------- ------------------------- ---------- 101 Kochhar 100 108 Greenberg 101 109 Faviet 108 110 Chen 108 111 Sciarra 108 112 Urman 108 113 Popp 108 200 Whalen 101 . . .
The next example is similar to the preceding example, but uses the LEVEL
pseudocolumn to show parent and child rows:
SELECT employee_id, last_name, manager_id, LEVEL FROM employees CONNECT BY PRIOR employee_id = manager_id; EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL ----------- ------------------------- ---------- ---------- 101 Kochhar 100 1 108 Greenberg 101 2 109 Faviet 108 3 110 Chen 108 3 111 Sciarra 108 3 112 Urman 108 3 113 Popp 108 3 . .
See Also:
|
|
![]() Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|