DB2 supports recursive common table expressions that can be used to implement a classic bill-of-materials parts explosion. I like using DB2 examples because they provide some insight into the ANSI SQL standards. To illustrate this, I will borrow an example from IBM's SQL Reference "Appendix M. Recursion Example." This uses the example of a Partlist table shown below:

CREATE TABLE PARTLIST (PART VARCHAR(8), SUBPART VARCHAR(8), QUANTITY INTEGER);

To explode this table into the parts and subparts, the following query will be used:

WITH RPL (PART, SUBPART, QUANTITY) AS
  ( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
     FROM PARTLIST ROOT
     WHERE ROOT.PART = '01'
   UNION ALL
     SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
     FROM RPL PARENT, PARTLIST CHILD
     WHERE PARENT.SUBPART = CHILD.PART
   )
SELECT DISTINCT PART, SUBPART, QUANTITY
   FROM RPL
     ORDER BY PART, SUBPART, QUANTITY;

The above query uses a common table expression, called RPL (recursive partlist) in this case. The first part of the query retrieves the direct children of Part '01'. (In effect, when performing a recursive query, you need to start somewhere, and this is where we will begin this one.) The remainder of the query in the subselect joins the subselect rows to those already selected by referring to the query name RPL and using that as the parent that is then joined to the child. In other words, once a row is returned in RPL, it is used as a parent and then used to join to the child row. This recursive query can then be used in a Select statement by its name, RPL, as shown by the second SQL statement above.

This type of statement is very difficult to understand, and may need to be studied and used to fully appreciate how it is working. It is certainly not one of SQL's more intuitive statements.

The Select Distinct in the second query ensures that the same part and subpart is listed only once.