Hierarchical Queries

Syntax

SELECT [level], column FROM table
START WITH condition
CONNECT BY[PRIOR column1 = column2|column1 = prior column2]
[ORDER SIBLINGS BY] [GROUP BY]

Parameters

• level: specifies the pseudocolumn to return the query level.
• START WITH: specifies the hierarchy, that is, the root row of the query.
• CONNECT BY: specifies the relationship between parent rows and child rows.
• PRIOR: specifies the parent level.
• ORDER SIBLINGS BY: specifies the sequence in the same level.

Examples

-- Create a table and insert data into the table.
gaussdb=# CREATE TABLE area (id INT,name VARCHAR(25),parent_id INT);
gaussdb=# INSERT INTO area VALUES (1,'China',NULL);
gaussdb=# INSERT INTO area VALUES (2,'Beijing',1);
gaussdb=# INSERT INTO area VALUES(3,'Chaoyang District',2);
gaussdb=# INSERT INTO area VALUES(4,'Shaanxi Province',1);
gaussdb=# INSERT INTO area VALUES(5,'Xi'an',4);
gaussdb=# INSERT INTO area VALUES(6,'Yanta District',5);
gaussdb=# INSERT INTO area VALUES(7,'Weiyang District',5);

-- Query the hierarchy.
gaussdb=# SELECT level, name FROM area 
    START WITH (id = 1) 
    CONNECT BY PRIOR id = parent_id;
 level |  name  
-------+--------
     1 | China
     2 | Shaanxi Province
     3 | Xi'an
     4 | Weiyang District
     4 | Yanta District
     2 | Beijing
     3 | Chaoyang District
(7 rows)

• Use WHERE to remove a node (only Beijing).

  -- Use WHERE to remove a node (only Beijing).
  gaussdb=# SELECT level, name FROM area 
      WHERE id <> 2 
      START WITH (id = 1) 
      CONNECT BY PRIOR id = parent_id;
   level |  name  
  -------+--------
       1 | China
       2 | Shaanxi Province
       3 | Xi'an
       4 | Weiyang District
       4 | Yanta District
       3 | Chaoyang District
  (6 rows)

• Add a condition to the CONNECT BY PRIOR clause to remove a branch (Beijing and its sub-regions).

  gaussdb=# SELECT level, name FROM area 
      START WITH (id = 1) 
      CONNECT BY PRIOR id = parent_id AND id <> 2;
   level |  name  
  -------+--------
       1 | China
       2 | Shaanxi Province
       3 | Xi'an
       4 | Weiyang District
       4 | Yanta District
  (5 rows)
  
  -- Delete.
  gaussdb=# DROP TABLE area;