Hierarchical query operators

The hierarchical query operators include PRIOR and CONNECT_BY_ROOT. These two operators are valid only in hierarchical queries.

PRIOR operator

In a hierarchical query, the CONNECT BY condition must include at least one expression that is prefixed with the PRIOR operator. The result of the PRIOR operator is obtained by using the parent row of the current row to calculate the subsequent expression.

The PRIOR operator is most commonly used in scenarios involving equality comparisons between columns (it can appear on either side of the comparison operator). Theoretically, other operators besides the equality operator (=) can be used in the CONNECT BY clause. However, conditions created using these other operators may result in cycles. In such cases, OceanBase Database will detect the cycle at runtime and return an error.

CONNECT_BY_ROOT operator

CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. The parameter of CONNECT_BY_ROOT can be a column or most expressions. The result of the CONNECT_BY_ROOT operator is obtained by using the data of the root row to calculate the value of the parameter.

For more information about hierarchical queries, see Hierarchical queries.