For a specified row, its parent node and its child nodes are determined by the CONNECT BY clause. The CONNECT BY clause must consist of two expressions compared with the equals (=) operator. One of these two expressions must be preceded by the keyword PRIOR.

To determine the child nodes of a specified row, perform the following steps:

  1. Evaluate parent_expr on the specified row.
  2. Evaluate child_expr on other rows resulting from the evaluation of table_expression.
  3. If parent_expr = child_expr, this row is a child node of the specified parent row.
  4. Repeat the process for all remaining rows in table_expression. All rows that satisfy the equation in step 3 are the child nodes of the specified parent row.
Note The evaluation process checks whether a row is a child node occurs on every row returned by table_expression. Then, the WHERE clause is used in table_expression.

By repeating this process, you can regard each child node found in the preceding steps as a parent and build an inverted tree of nodes. The process is complete when the final set of child nodes has no child nodes. These nodes are the leaf nodes.

A SELECT statement that includes a CONNECT BY clause includes the START WITH clause.
Note The START WITH clause determines the rows that are the root nodes. For example, the rows are the initial parent nodes on which the preceding algorithm is used. For more information, see Select root nodes.

Examples

Create a city hierarchy table. 
CREATE TABLE chinamap(id INT, parentid INT, name TEXT);
Note id indicates the ID of the child object, parentid indicates the ID of the parent object, and name indicates the name of the child object.
Insert data.
  • Insert the data of Zhejiang Province. 
    INSERT INTO chinamap VALUES(1, NULL, 'Zhejiang');
  • Insert the data of prefecture-level cities in Zhejiang Province. 
    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');
  • Insert the data of Hangzhou districts. 
    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 city hierarchy. 
select * from chinamap start with id = 1 connect by parentid = prior id;
A similar output is displayed:
 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)