Hierarchical queries

A hierarchical query is a special type of query that displays hierarchical data in a hierarchical manner.

Hierarchical data refers to data in a relational table that has a hierarchical relationship. This type of relationship is very common in real life. Here are some examples:

• The relationship between a team leader and team members in an organizational structure

• The relationship between superior and subordinate departments in a company

• The relationship between page jumps on a web page

Limitations and considerations

If the FOR UPDATE clause is used in a hierarchical query, the following scenarios are not supported:

• If the subquery uses the DISTINCT keyword or aggregate functions, it cannot be used with FOR UPDATE.
• Any scenario involving common table expressions (CTEs) is not supported. That is, a SELECT query with the WITH ... AS ... clause cannot be used with FOR UPDATE. For more information about the WITH ... AS ... clause, see WITH CLAUSE.

To ensure normal database operation and performance optimization, avoid using the FOR UPDATE clause in the aforementioned scenarios.

Syntax

The syntax for a simple hierarchical query is as follows:

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 ... ]
[FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]

For more information about the SELECT syntax, see SIMPLE SELECT.

Parameters

Execution process

Understanding the execution process is crucial for using and implementing hierarchical queries. The general execution process for hierarchical queries is as follows:

1. Execute the SCAN or JOIN operations specified after FROM.

2. Generate the hierarchical relationship result based on the contents of START WITH and CONNECT BY.

   The process for generating the hierarchical relationship in step 2 can be understood as follows:

   1. Obtain the root rows (Root Rows) based on the expression in START WITH.

   2. Select the child rows (Child Rows) for each root row (Root Rows) based on the expression in CONNECT BY.

3. Use the child rows (Child Rows) generated in step 2 as new root rows (Root Rows) to generate further child rows (Child Rows). This process is repeated until no new rows are generated.

4. Execute the remaining clauses (such as WHERE, GROUP, ORDER BY, etc.) according to the standard query execution process.

Examples

1. Create a table named emp and insert data into the emp_id, position, and mgr_id columns.

   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 Manager',1);
   INSERT INTO emp VALUES (3,'Asia-Pacific Manager',1);
   INSERT INTO emp VALUES (4,'Americas Manager',1);
   INSERT INTO emp VALUES (5,'Italy Manager',2);
   INSERT INTO emp VALUES (6,'France Manager',2);
   INSERT INTO emp VALUES (7,'China Manager',3);
   INSERT INTO emp VALUES (8,'Korea Manager',3);
   INSERT INTO emp VALUES (9,'Japan Manager',3);
   INSERT INTO emp VALUES (10,'US Manager',4);
   INSERT INTO emp VALUES (11,'Canada Manager',4);
   INSERT INTO emp VALUES (12,'Beijing Manager',7);
   

   The position column in the preceding example has a clear hierarchical relationship. The hierarchical structure is as follows:

   

2. Execute the following statement to display the result in a hierarchical format.

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

   The returned result is as follows:

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

3. Query only the "Asia-Pacific" hierarchy and use the FOR UPDATE clause to lock the query result.

   SELECT emp_id, mgr_id, position, LEVEL
   FROM emp
   START WITH position = 'Asia-Pacific Manager'
       CONNECT BY PRIOR emp_id = mgr_id
   FOR UPDATE;
   

   The returned result is as follows:

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

References

• SIMPLE SELECT
• WITH CLAUSE
• Lock query result by using SELECT FOR UPDATE