Perform hierarchical queries with START WITH and CONNECT BY PRIOR clauses - PolarDB

The emp table in the sample application stores employee records in a tree-structured hierarchy. Each row includes a mgr column that holds the employee number of that employee's manager, establishing a parent-child relationship between rows.

Key properties of this data model:

Each employee has at most one manager (mgr stores a single employee number).
An employee can manage multiple subordinates.
KING is the company president and has no manager, so the mgr column for KING is null.

How hierarchical queries work

PolarDB processes a hierarchical query using START WITH and CONNECT BY PRIOR as follows:

Select the root node. START WITH mgr IS NULL selects the row where mgr is null—KING (employee number 7839)—as the root.
Identify child nodes. CONNECT BY PRIOR empno = mgr defines the parent-child relationship: a row is a child of the current row if its mgr value equals the current row's empno. JONES, BLAKE, and CLARK all have mgr = 7839, so they are direct reports of KING.
Expand each generation. The database repeats step 2 for each newly selected row. For JONES (empno 7566), SCOTT and FORD have mgr = 7566, so they become JONES's children. This continues until no more child rows exist.
Traverse depth-first. The query walks each branch completely before moving to the next sibling, visiting nodes from top to bottom and left to right.

Example query and output

The following SELECT statement retrieves all employees in hierarchical order, starting from the root:

SELECT ename, empno, mgr
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

Output:

 ename  | empno | mgr
--------+-------+------
 KING   |  7839 |
 JONES  |  7566 | 7839
 SCOTT  |  7788 | 7566
 ADAMS  |  7876 | 7788
 FORD   |  7902 | 7566
 SMITH  |  7369 | 7902
 BLAKE  |  7698 | 7839
 ALLEN  |  7499 | 7698
 WARD   |  7521 | 7698
 MARTIN |  7654 | 7698
 TURNER |  7844 | 7698
 JAMES  |  7900 | 7698
 CLARK  |  7782 | 7839
 MILLER |  7934 | 7782
(14 rows)

The depth-first traversal explains the output order: after KING, the query follows JONES's branch completely (JONES → SCOTT → ADAMS, then FORD → SMITH) before moving to BLAKE's branch, and finally CLARK's branch.