All Products
Search
Document Center

Hierarchical queries

Last Updated: Jun 18, 2021

A hierarchical query is a query statement that has special features. You can use this query to display the hierarchical data based on the hierarchical relationship. Hierarchical data indicates that the data in a relational table has the hierarchical relationship. Hierarchical relationships are common in real life. For example, the following hierarchical relationships are common:

  • Relationships between team leaders and team members in the organizational structure

  • Relationships between superior and subordinate departments in an enterprise

  • Relationships between page redirects in web pages

Syntax

SELECT [level], column, expr... FROM table [WHERE condition] [ START WITH start_expression ]
CONNECT BY [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr }
[ ORDER SIBLINGS BY …] [ GROUP BY … ] [ HAVING … ] [ ORDER BY … ]

Parameters

Parameter

Description

LEVEL

The node level. This parameter is a pseudocolumn and specifies the level. The level is counted from the beginning of a query and numbered as 1. The level of the second node in the query is 2, the level for the third node in the query is 3, and so on.

CONNECT_BY_ISLEAF

Specifies whether the current data row is a leaf node in the hierarchical relationship. This parameter is a pseudocolumn. The value 0 indicates that the row is not a leaf node. The value 1 indicates that the row is a leaf node.

CONNECT_BY_ISCYCLE

Specifies whether the current data row is in the loop. This parameter is a pseudocolumn. The value 0 indicates that the current data row is not in the loop. The value 1 indicates that the current data row is in the loop.

CONNECT_BY_ROOT operator

CONNECT_BY_ROOT is a unary operator and indicates that the columns in parameters come from the root node of a hierarchical query. This operator has the same precedence as the unary plus (+) and unary minus (-) operators.

condition

The condition.

CONNECT BY

Specifies how to determine the parent-child relationship. An equivalent expression is usually used. Other expressions are also supported.

START WITH

Specifies the root row in the hierarchical query.

PRIOR operator

PRIOR is a unary operator and indicates that the columns in parameters come from the parent row. This operator has the same precedence as the unary plus (+) and unary minus (-) operators.

NOCYCLE

When you specify this keyword, the result can still be returned even if the result contains a loop. You can use the CONNECT_BY_ISCYCLE virtual column to specify where the loop occurs. When you do not specify this keyword, the client receives an error if a loop occurs.

ORDER SIBLINGS BY

Specifies the order in which rows of the same level are sorted.

Execution process

The key to using and implementing hierarchical queries is to understand their execution processes. The following execution process for a hierarchical query is provided:

  1. Perform the SCAN or JOIN operation that is after FROM.

  2. Generate the hierarchical relationship results by using the content of START WITH and CONNECT BY.

  3. Execute the remaining clauses, such as WHERE, GROUP, and ORDER BY, based on the regular query execution process. The following process explains how to generate hierarchical relationships in Step 2.

  4. Obtain the root rows by using the expression in START WITH.

  5. Select the child rows of each root row by using the expression in CONNECT BY.

  6. Use the child rows generated in Step 2 as the new root rows and further generate child rows. This process is repeated until no new rows are generated.

Examples

The following example shows how to use hierarchical queries. Insert data into the emp_id, position, and mgr_id columns of the emp table. Execute the following statements:

CREATE TABLE emp(emp_id INT,position VARCHAR(50),mgr_id INT);
INSERT INTO emp VALUES (1,'Global Manager',NULL);
INSERT INTO emp VALUES (2,'Europe Regional Manager',1);
INSERT INTO emp VALUES (3,'Asia Pacific Regional Manager',1);
INSERT INTO emp VALUES (4,'Americas Regional Manager',1);
INSERT INTO emp VALUES (5,'Italy Regional Manager',2);
INSERT INTO emp VALUES (6,'France Regional Manager',2);
INSERT INTO emp VALUES (7,'China Regional Manager',3);
INSERT INTO emp VALUES (8,'Korea Regional Manager',3);
INSERT INTO emp VALUES (9,'Japan Regional Manager',3);
INSERT INTO emp VALUES (10,'US Regional Manager',4);
INSERT INTO emp VALUES (11,'Canada Regional Manager',4);
INSERT INTO emp VALUES (12,'Beijing Regional Manager',7);

The preceding content shows that data in the position column has a clear hierarchical relationship.

To display the results by using the hierarchy, execute the following statement:

SELECT emp_id, mgr_id, position, level FROM emp
START WITH mgr_id IS NULL CONNECT BY PRIOR emp_id = mgr_id;

The following query result is returned:

+--------+--------+-------------------+-------+
| EMP_ID | MGR_ID | POSITION          | LEVEL |
+--------+--------+-------------------+-------+
|      1 |    NULL| Global Manager           |     1 |
|      2 |      1 | Europe Regional Manager         |     2 |
|      5 |      2 | Italy Regional Manager       |     3 |
|      6 |      2 | France Regional Manager         |     3 |
|      3 |      1 | Asia Pacific Regional Manager         |     2 |
|      7 |      3 | China Regional Manager         |     3 |
|     12 |      7 | Beijing Regional Manager         |     4 |
|      8 |      3 | Korea Regional Manager         |     3 |
|      9 |      3 | Japan Regional Manager         |     3 |
|      4 |      1 | Americas Regional Manager         |     2 |
|     10 |      4 | US Regional Manager         |     3 |
|     11 |      4 | Canada Regional Manager       |     3 |
+--------+--------+-------------------+-------+

To query the hierarchy of only the Asia Pacific region, execute the following statement:

SELECT emp_id, mgr_id, position, level FROM emp START WITH position = 'Asia Pacific Regional Manager' CONNECT BY PRIOR emp_id = mgr_id;

The following query result is returned:

+--------+--------+----------------+-------+
| EMP_ID | MGR_ID | POSITION       | LEVEL |
+--------+--------+----------------+-------+
|      3 |      1 | Asia Pacific Regional Manager      |     1 |
|      7 |      3 | China Regional Manager      |     2 |
|     12 |      7 | Beijing Regional Manager      |     3 |
|      8 |      3 | Korea Regional Manager      |     2 |
|      9 |      3 | Japan Regional Manager      |     2 |
+--------+--------+----------------+-------+