|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:
WITHspecifies the root row(s) of the hierarchy.
BYspecifies the relationship between parent rows and child rows of the hierarchy. In a hierarchical query, one expression in
conditionmust be qualified with the
PRIORoperator to refer to the parent row. For example,
condition is compound, then only one condition requires the
PRIOR operator. For example:
In addition, the
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
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:
WHEREpredicate contains a join, Oracle applies the join predicates before doing the
WHEREclause does not contain a join, Oracle applies all predicates other than the
BYpredicates after doing the
BYprocessing 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:
WHEREclause either before or after the
BYclause depending on whether the
WHEREclause contains any join predicates (as described in the preceding bullet list).
BYcondition with respect to one of the root rows.
BYcondition with respect to a current parent row.
WHEREclause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the
WHEREclause. 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
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
BY condition can contain other conditions to further filter the rows selected by the query. The
BY condition cannot contain a subquery.
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.
In a hierarchical query, do not specify either
The following hierarchical query uses the
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 . .