Learning Visually with Examples [Electronic resources]

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