All Products
Search
Document Center

Hierarchical query operators

Last Updated: Jun 18, 2021

The PRIOR and CONNECT_BY_ROOT operators are valid in only hierarchical queries.

PRIOR operator

In a hierarchical query, the CONNECT BY condition must contain at least one expression that is qualified by the PRIOR operator. The result of the PRIOR operator is obtained by using the parent row of the current row to compute the expression that immediately follows the operator.

PRIOR is most commonly used in an equality comparison of columns. PRIOR can be placed on either side of the comparison operator. Theoretically, operators other than the equal sign (=) can be used in CONNECT BY clauses. However, the conditions that are created by these other operators may cause loops. In this case, ApsaraDB for OceanBase detects the loops at runtime and returns an error.

CONNECT_BY_ROOT operator

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

You cannot specify this operator in a START WITH or CONNECT BY condition.