The CONNECT BY clause determines the parent-child relationship of rows when a hierarchical query is run.

The following code block shows the general syntax of the CONNECT BY clause:

CONNECT BY { PRIOR parent_expr = child_expr |
 child_expr = PRIOR parent_expr }

parent_expr is evaluated for a candidate parent row. If the parent_expr = child_expr condition is evaluated to TRUE for a row that is returned by the FROM clause, this row is considered as a child row of the parent row.

You can specify the following optional clause in conjunction with the CONNECT BY clause: 

START WITH start_expression

If start_expression is evaluated to TRUE for a row that is returned by the FROM clause, this row becomes a root node of the hierarchy. 

ORDER SIBLINGS BY expression [ ASC | DESC ] [, ...]

Sibling rows of the hierarchy are ordered by expression in the result set.

Note In PolarDB-O, you can use only the AND operator in the CONNECT BY clause and cannot use the other operators in the clause.

CONNECT_BY_ISLEAF

CONNECT_BY_ISLEAF determines whether the current row is a leaf node. If the current row is a leaf node, 1 or true is returned. Otherwise, 0 or false is returned.

The following code provides an example of the SELECT statement where CONNECT_BY_ISLEAF is used: 

SELECT last_name "Employee", CONNECT_BY_ISLEAF "IsLeaf",
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE LEVEL <= 3 AND department_id = 80
   START WITH employee_id = 100
   CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;

The following example shows the returned result:

Employee            IsLeaf      LEVEL Path
--------------- ---------- ---------- -----------------------------------
Russell                  0          2 /King/Russell
Tucker                   1          3 /King/Russell/Tucker
Bernstein                1          3 /King/Russell/Bernstein
Hall                     1          3 /King/Russell/Hall
Olsen                    1          3 /King/Russell/Olsen
Cambrault                1          3 /King/Russell/Cambrault
Tuvault                  1          3 /King/Russell/Tuvault
Partners                 0          2 /King/Partners
King                     1          3 /King/Partners/King
Sully                    1          3 /King/Partners/Sully
McEwen                   1          3 /King/Partners/McEwen
Smith                    1          3 /King/Partners/Smith
Doran                    1          3 /King/Partners/Doran
Sewall                   1          3 /King/Partners/Sewall
Errazuriz                0          2 /King/Errazuriz
Vishney                  1          3 /King/Errazuriz/Vishney
...

34 rows selected.

CONNECT_BY_ROOT

CONNECT_BY_ROOT is a unary operator and is valid in only hierarchical queries. When you use this operator to qualify a column, the column value is returned by using the data in the root row. This operator returns the immediate parent row and all the ancestor rows in the hierarchy. This extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries.

The following code provides an example of the SELECT statement where CONNECT_BY_ROOT is used: 

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE LEVEL > 1 and department_id = 110
   CONNECT BY PRIOR employee_id = manager_id;

The following example shows the returned result:

Employee        Manager         Pathlen Path
--------------- ------------ ---------- -----------------------------------
Higgins         Kochhar               1 /Kochhar/Higgins
Gietz           Kochhar               2 /Kochhar/Higgins/Gietz
Gietz           Higgins               1 /Higgins/Gietz
Higgins         King                  2 /King/Kochhar/Higgins
Gietz           King                  3 /King/Kochhar/Higgins/Giet