Section 9.5. Recursive SQL

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

[View full width]

 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