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.
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