PolarDB:Hierarchical queries

A hierarchy is typically represented by an inverted tree structure. The tree contains interconnected nodes. Each node may be connected to none, one, or multiple child nodes. Each node is connected to one parent node except for the top node which has no parent. This node is the root node. Each tree has only one root node. Nodes that do not have any child nodes are called leaf nodes. A tree always has at least one leaf node. For example, a tree contains only a single node. In this case, this node is both the root and the leaf.

In a hierarchical query, the rows of the result set represent the nodes of one or more trees.

The hierarchical relationship in a query is described by the CONNECT BY clause. This clause forms the basis of the order in which rows in the result set are returned. The following example shows how the CONNECT BY clause and its associated optional clauses are used in the SELECT statement.

SELECT select_list FROM table_expression [ WHERE ...]
  [ START WITH start_expression ]
    CONNECT BY { PRIOR parent_expr = child_expr |
 child_expr = PRIOR parent_expr }
  [ ORDER SIBLINGS BY column1 [ ASC | DESC ]
      [, column2 [ ASC | DESC ] ] ...
  [ GROUP BY ...]
  [ HAVING ...]
  [ other ...]

select_list is one or more expressions that comprise the fields of the result set. table_expression is one or more tables or views from which the rows of the result set originate. other is any additional valid SELECT statement. The following sections describe the clauses pertinent to hierarchical queries, including START WITH, CONNECT BY, and ORDER SIBLINGS BY.