SQL Advanced Queries
hierarchical query
Hierarchical structure can be understood as a tree-like data structure, consisting of nodes. For example, a common organizational structure consists of a general manager, multiple deputy general managers, and multiple department heads. Another example is in manufacturing a product will have multiple sub-parts. A simple example is shown in the following diagram
The car serves as the root node and contains below it two child nodes, engine and body, which in turn are composed of other leaf nodes. (Leaf nodes denote nodes with no children)
If we were to store this product information in a database, the following data table would be formed.
We use the parent_product_id column to indicate which product is the parent of the current product.
So how do you do hierarchical queries with SQL statements? This is where the CONNECT BY and START WITH syntaxes come in.
Let's write out the SQL and then explain what it means.
SELECT
level,
id,
parent_product_id,
name
FROM
product
START WITH id = 1
CONNECT BY prior id = parent_product_id
ORDER BY
level
The query results are as follows:
Explain: LEVEL column indicates that the current product belongs to the first level. START WITH indicates from which product to start the query, CONNECT BY PRIOR indicates that the relationship between the parent node and the child node, each product ID points to a parent product.
If we change the starting point of the START WITH query to id = 2 and re-run the above SQL statement we will get the following result:
Since the product with id=2 is a body, we will only be able to check the sub-products under the body.
Of course, we can beautify the query results to make it more hierarchical, we let the root node below the LEVEL in front of a few spaces can be. Modify the above SQL a bit. Add 2*(LEVEL-1) spaces in front of each LEVEL, so that the second level will add two spaces and the third level will add four spaces.
SELECT
level,
id,
parent_product_id,
LPAD(' ', 2 * (level - 1)) || name AS name
FROM
product
START WITH id = 1
CONNECT BY prior id = parent_product_id
The query results have been layered as shown below:
recursive query
In addition to using the method we described above, you can also use a recursive query to get the same results. Recursion uses the WITH statement. A normal WITH statement can be viewed as a subquery, and we can use the contents of this subquery directly outside of WITH.
When querying recursively, we are referencing this subquery inside the WITH statement. Still in the above example, we use the WITH statement for the query.
WITH
temp_product (product_level, id, parent_product_id,name) AS
(
SELECT
0 AS product_level,id,parent_product_id,name
FROM
product
WHERE
parent_product_id IS NULL
UNION ALL
SELECT
tp.product_level + 1,,
p.parent_product_id,
FROM
product p
JOIN temp_product tp
ON
p.parent_product_id=
)
SELECT
product_level,
id,
parent_product_id,
LPAD(' ', 2 * product_level)
|| name AS NAME
FROM
temp_product;
The first SELECT statement looks up the root node and sets it to level = 0. The second SELECT statement is associated with the WITH statement itself, and the level is recursive by adding 1 to each level.
The query results are as follows:
You can see that the first column is the product hierarchy of the display, which is consistent with the results from our query above.
Also when using WITH recursion you can use depth-first and breadth-first search, what does that mean? Breadth-first means returning the sibling rows before returning the child rows, as in the above figure, which returns the body and engine rows first, followed by the child rows below them. On the contrary, depth-first is to first return the child rows of a parent node before returning the other sibling row.
All we need to do is add the following statement above the SELECT statement to implement a depth-first search query.
search depth FIRST BY id
SET order_by_id
The result is as follows, seeing that the child rows under each parent node are returned first, and then the other parent node.
Similarly, breadth-first uses the following SQL statement
search breadth FIRST BY id
SET order_by_id