Hierarchical query

A hierarchical query is a special query that can present hierarchical data in a hierarchical order.

Hierarchical data refers to data with a hierarchical relationship in relational tables. This type of relationship is very common in our daily life. Here are some examples:

• Relationship between team leaders and team members

• Relationship between upper-level and lower-level departments in enterprises

• Relationship between source and destination web pages in page jumping scenarios

Limitations and considerations

You cannot use the FOR UPDATE clause in a hierarchical query in the following scenarios:

• A subquery uses the DISTINCT keyword or an aggregate function.
• A Common Table Expression (CTE) is used. In other words, you cannot use the FOR UPDATE clause in a SELECT query if the query already contains a WITH ... AS ... clause. For more information about the WITH ... AS ... clause, see WITH CLAUSE.

For normal operations and performance optimization of the database, do not use the FOR UPDATE clause in the preceding two scenarios.

Syntax

The syntax of the SELECT statement 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 statement, see SIMPLE SELECT.

Parameters

Execution process

Before you take use of a hierarchical query, you must understand its execution process. The following describes the general execution process of a hierarchical query:

1. Execute the SCAN or JOIN operation after the FROM clause.

2. Generate a hierarchical relationship based on START WITH and CONNECT BY.

   The procedure for generating a hierarchical relationship is as follows:

   1. Obtain the root rows based on the expression in START WITH.

   2. Select the child rows of each root row based on the expression in CONNECT BY.

3. Use the child rows generated in Step 2 as the new root rows and generate child rows for them. Repeat these steps cyclically until no new rows are generated.

4. Execute the rest clauses such as WHERE, GROUP, and ORDER BY based on the general query execution process.

Examples

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

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

   In the preceding table, the position column has a clear hierarchical relationship.

2. Execute the following statement to present the results in a hierarchical structure:

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

   The return result is as follows:

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

3. Execute the following statement to query 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 = 'Manager of Asia-Pacific region'
       CONNECT BY PRIOR emp_id = mgr_id
   FOR UPDATE;
   

   The return result is as follows:

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

References

• SIMPLE SELECT
• WITH CLAUSE
• Use the SELECT FOR UPDATE statement to lock query results