Hierarchical query operators

The hierarchical query operators include PRIOR and CONNECT_BY_ROOT, which are valid only in hierarchical queries.

PRIOR

In a hierarchical query, the CONNECT BY condition should contain at least one expression that is qualified by the PRIOR operator. The PRIOR operator evaluates the immediately following expression for the parent row of the current row in a hierarchical query.

PRIOR is most commonly used in equality comparisons on columns. The PRIOR keyword can be placed on either side of the comparison operator. Technically, in addition to the equal operator (=), you can use other operators in the CONNECT BY clause. However, the conditions created by other operators can cause a loop. In this case, OceanBase Database detects the loop at run time and returns an error.

CONNECT_BY_ROOT

CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. You can specify a column or most expressions as a parameter of the CONNECT_BY_ROOT operator. The CONNECT_BY_ROOT operator returns values using data from the root row.

For more information about hierarchical queries, see Hierarchical query.