Hierarchical queries

A hierarchical query is a special type of query that allows you to display 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 upper and lower-level departments in a company

• The relationship between page jumps on a web page

Limitations and considerations

If a hierarchical query contains the FOR UPDATE clause, the following scenarios are not supported:

• If a subquery uses the DISTINCT keyword or an aggregate function, it cannot be used with FOR UPDATE.
• Any scenario that includes 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 above 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 the FROM clause.

2. Generate the hierarchical relationship results based on the content of START WITH and CONNECT BY.

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

   1. Use the expression in START WITH to obtain the root rows (Root Rows).

   2. Use the expression in CONNECT BY to select the child rows (Child Rows) for each root row (Root Row).

3. Use the child rows (Child Rows) generated in step 2 as new root rows (Root Rows) to generate further child rows (Child Rows), repeating this process 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 table above has a clear hierarchical relationship. The tree structure is as follows:

   

2. Execute the following statement to display the results 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 hierarchical structure of the "Asia-Pacific" region and use the FOR UPDATE clause to lock the query results.

   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 results with SELECT FOR UPDATE