PolarDB:Define parent-child relationships

How it works

For each row in table_expression, the database evaluates the CONNECT BY condition to determine child nodes:

1. Evaluate parent_expr on the current (parent) row.

2. Evaluate child_expr on every other row in table_expression.

3. If parent_expr = child_expr, that row is a child node of the current row.

4. Repeat steps 1–3 for each child node found, treating each as a new parent.

The traversal ends when a set of child nodes has no further child nodes — these are the leaf nodes.

The evaluation process runs on every row returned by table_expression. The WHERE clause is then applied to the result.

A SELECT statement that includes a CONNECT BY clause also includes the START WITH clause. The START WITH clause seeds the traversal by selecting the initial root nodes — the rows on which the algorithm starts. For more information, see Select root nodes.

Example

The following example uses a city hierarchy to demonstrate a three-level structure: province → prefecture-level cities → districts.

Create the table:

CREATE TABLE chinamap(id INT, parentid INT, name TEXT);

• id: ID of the child object

• parentid: ID of the parent object

• name: name of the child object

Insert data:

-- Province
INSERT INTO chinamap VALUES(1, NULL, 'Zhejiang');

-- Prefecture-level cities in Zhejiang
INSERT INTO chinamap VALUES(101, 1, 'Hangzhou');
INSERT INTO chinamap VALUES(102, 1, 'Ningbo');
INSERT INTO chinamap VALUES(103, 1, 'Wenzhou');
INSERT INTO chinamap VALUES(104, 1, 'Jiaxing');
INSERT INTO chinamap VALUES(105, 1, 'Huzhou');
INSERT INTO chinamap VALUES(106, 1, 'Shaoxing');
INSERT INTO chinamap VALUES(107, 1, 'Jinhua');
INSERT INTO chinamap VALUES(108, 1, 'Quzhou');
INSERT INTO chinamap VALUES(109, 1, 'Zhoushan');
INSERT INTO chinamap VALUES(110, 1, 'Taizhou');
INSERT INTO chinamap VALUES(111, 1, 'Lishui');

-- Districts in Hangzhou
INSERT INTO chinamap VALUES(10101, 101, 'Shangcheng');
INSERT INTO chinamap VALUES(10102, 101, 'Xiacheng');
INSERT INTO chinamap VALUES(10103, 101, 'Xihu');
INSERT INTO chinamap VALUES(10104, 101, 'Gongshu');
INSERT INTO chinamap VALUES(10105, 101, 'Yuhang');
INSERT INTO chinamap VALUES(10106, 101, 'Binjiang');
INSERT INTO chinamap VALUES(10107, 101, 'Jianggan');
INSERT INTO chinamap VALUES(10108, 101, 'Xiaoshan');

Query the full hierarchy starting from Zhejiang:

This query starts at the province row (id = 1) and traverses down by matching each row's parentid to the id of its parent.

SELECT * FROM chinamap START WITH id = 1 CONNECT BY parentid = PRIOR id;

The output is similar to:

 id    | parentid | name
-------+----------+------------
     1 |          | Zhejiang
   101 |        1 | Hangzhou
 10101 |      101 | Shangcheng
 10102 |      101 | Xiacheng
 10103 |      101 | Xihu
 10104 |      101 | Gongshu
 10105 |      101 | Yuhang
 10106 |      101 | Binjiang
 10107 |      101 | Jianggan
 10108 |      101 | Xiaoshan
   102 |        1 | Ningbo
   103 |        1 | Wenzhou
   104 |        1 | Jiaxing
   105 |        1 | Huzhou
   106 |        1 | Shaoxing
   107 |        1 | Jinhua
   108 |        1 | Quzhou
   109 |        1 | Zhoushan
   110 |        1 | Taizhou
   111 |        1 | Lishui
(20 rows)

The result traverses depth-first: Zhejiang → Hangzhou → all Hangzhou districts → Ningbo → Wenzhou → and so on through the remaining prefectures. The Hangzhou district rows (10101–10108) are leaf nodes because they have no child rows.

What's next

• Select root nodes