Hierarchical queries
Hierarchical queries are a form of recursive query that provides support for retrieving a hierarchy, such as a bill of materials, from relational data using a CONNECT BY clause.
Connect-by recursion uses the same subquery for the seed (start) and the recursive step (connect). This combination provides a concise method of representing recursions such as bills-of-material, reports-to-chains, or email threads.
Connect-by recursion returns an error if a cycle occurs. A cycle occurs when a row produces itself, either directly or indirectly. Using the optional CONNECT BY NOCYCLE clause, the recursion can be directed to ignore the duplicated row, thus avoiding both the cycle and the error.
subselect
Hierarchical query support includes the following extensions to the subselect.
- The subselect includes a hierarchical-query-clause
- The clauses of the subselect are processed in the following sequence:
- FROM clause
- hierarchical-query-clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- SELECT clause
- ORDER BY clause
- OFFSET clause
- FETCH clause
- If the subselect includes a hierarchical-query-clause,
special rules apply for the order of processing the predicates in
the WHERE clause. The search-condition is factored into predicates
along with its AND conditions (conjunction). If a predicate is an
implicit join predicate (that is, it references more than one table
in the FROM clause), the predicate is applied before the hierarchical-query-clause.
Any predicate referencing at most one table in the FROM clause is
applied to the intermediate result table of the hierarchical-query-clause.
A hierarchical query involving joins should be written using explicit joined tables with an ON clause to avoid confusion about the application of WHERE clause predicates.
- The ORDER SIBLINGS BY clause can be specified if the subselect includes a hierarchical-query-clause. This clause specifies that the ordering applies only to siblings within the hierarchies.