PolarDB:CONNECT BY clause

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.

Create test data

Create a table named employees and insert test data. See the following sample code:

-- Create the employees table.
CREATE TABLE employees (
  employee_id NUMBER(11) NOT NULL,
  last_name VARCHAR2(16) NOT NULL,
  manager_id NUMBER(11),
  department_id NUMBER(4) NOT NULL,
  CONSTRAINT pk_employees PRIMARY KEY (employee_id)
);

-- Insert test data.
INSERT INTO employees (employee_id, last_name, manager_id, department_id) VALUES
(100, 'King', NULL, 80),  -- Employee 100 is the manager of department 80.
(101, 'Blake', 100, 80),  -- Employee 101 is a subordinate of employee 100.
(102, 'Clark', 100, 80),  -- Employee 102 is a subordinate of employee 100.
(103, 'Miller', 101, 80), -- Employee 103 is a subordinate of employee 101.
(200, 'Kochhar', NULL, 110),  -- Employee 200 is the manager of department 110.
(201, 'Higgins', 200, 110),  -- Employee 201 is a subordinate of employee 200.
(202, 'Gietz', 200, 110),  -- Employee 202 is a subordinate of employee 200.
(203, 'Sciarra', 201, 110); -- Employee 203 is a subordinate of employee 201.

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        
----------+--------+-------+--------------------
 King     |      0 |     1 | /King
 Blake    |      0 |     2 | /King/Blake
 Miller   |      1 |     3 | /King/Blake/Miller
 Clark    |      1 |     2 | /King/Clark
(4 rows)

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           
----------+---------+---------+--------------------------
 Gietz    | Kochhar |       1 | /Kochhar/Gietz
 Higgins  | Kochhar |       1 | /Kochhar/Higgins
 Sciarra  | Kochhar |       2 | /Kochhar/Higgins/Sciarra
 Sciarra  | Higgins |       1 | /Higgins/Sciarra
(4 rows)