9.5. Recursive SQL
Recursive SQL is a powerful way to query hierarchies of data. Organizational structures, bills-of-material, product classifications, and document hierarchies are all examples of hierarchical data. Let's use an example to demonstrate how a recursive SQL statement is written.
Assume that there is a table called children with definition and date as shown in Figure 9.27.
Figure 9.27. Sample data in the children table
CREATE TABLE children ( person_id INTEGER
, name VARCHAR(50)
, age INTEGER
, gender CHAR(1)
, parent_id INTEGER )
PERSON_ID NAME AGE GENDER PARENT_ID
----------- -------- ----------- ----- -----------
1 Apple 10 F 10
2 Zoe 11 F 3
3 John 30 M 13
4 Mary 25 F 24
5 Peter 14 M 4
6 Jenny 13 F 4
24 Robert 60 M 30
7 record(s) selected.
|
To retrieve the ancestors of Jenny, you would use the recursive query shown in Figure 9.28.
Figure 9.28. A recursive SQL example
WITH temptab (person_id, name, parent_id) AS (1)
(SELECT person_id, name, parent_id (2)
FROM children
WHERE name = 'Jenny'
UNION ALL (3)
SELECT c.person_id, c.name, c.parent_id (4)
FROM children c, temptab super
WHERE c.person_id = super.parent_id
) SELECT * FROM temptab (5)
|
A common table expression (CTE) temporarily stores data as the query execution progresses. In Figure 9.28, the CTE is called temptab and it is created with the WITH clause at line (1). The definition of the CTE is specified at lines (2), (3), and (4) inside the parenthesis.
Line (2) obtains the initial result set which contains the record with the name 'Jenny'. Then, the recursion takes place by joining each row in temptab with its parents (4). The result of one execution of this recursion is added to temptab via UNION ALL at line (3).
The final query (5) extracts the person_id, name, and parent_id out of the temptab CTE.
The recursive SQL will return Jenny's parents and their parents, similar to Figure 9.29.
Figure 9.29. Result of a recursive SQL
PERSON_ID NAME PARENT_ID ----------- ----------------------------- ----------- SQL0347W The recursive common table expression "DB2ADMIN.TEMPTAB" may contain an infinite |
loop. SQLSTATE=01605
6 Jenny 4
4 Mary 24
24 Robert 30
3 record(s) selected with 1 warning messages printed.
Notice that a warning message is also returned indicating that the CTE may contain an infinite loop. To avoid an infinite loop, you can specify the maximum number of recursive levels in the query, as shown in Figure 9.30.
Figure 9.30. A recursive SQL example with a maximum number of recursive levels
WITH temptab (person_id, name, parent_id, level) AS
(SELECT person_id, name, parent_id, 1
FROM children
WHERE name = 'Jenny'
UNION ALL
SELECT c.person_id, c.name, c.parent_id, super.level + 1
FROM children c, temptab super
WHERE c.person_id = super.parent_id
AND level < 5
) SELECT * FROM temptab
|