Common
table expressions
A common table
expression is like a temporary view that is defined and used for the
duration of an SQL statement.
You can define a common
table expression wherever you can have a fullselect statement. For example, you
can include a common table expression in a SELECT, INSERT, SELECT INTO, or
CREATE VIEW statement.
Each common table
expression must have a unique name and be defined only once. However, you can
reference a common table expression many times in the same SQL statement.
Unlike regular views or nested table expressions, which derive their result
tables for each reference, all references to common table expressions in a
given statement share the same result table.
You can use a common
table expression in the following situations:
- When you want to avoid creating a view (when general
use of the view is not required, and positioned updates or deletes are not
used)
- When the desired result table is based on host
variables
- When the same result table needs to be shared in a
fullselect
- When the results need to be derived using recursion
Creating
a common table expression
Creating a common table
expression saves you the overhead of creating and dropping a regular view that
you need to use only once. Also, during statement preparation, DB2® does not
need to access the catalog for the view, which saves you additional overhead.
Use the WITH clause to create a common table
expression.
You can use a common
table expression in a SELECT statement by using the WITH clause at the
beginning of the statement.
Example: WITH clause in
a SELECT statement: The
following statement finds the department with the highest total pay. The query
involves two levels of aggregation. First, you need to determine the total pay
for each department by using the SUM function and order the results by using
the GROUP BY clause. You then need to find the department with highest total
pay based on the total pay for each department.
WITH DTOTAL (deptno, totalpay)
AS
(SELECT deptno, sum(salary+bonus)
FROM DSN8810.EMP
GROUP BY
deptno)
SELECT deptno
FROM DTOTAL
WHERE totalpay =
(SELECT max(totalpay)
FROM DTOTAL);
The result table for the
common table expression, DTOTAL, contains the department number and total pay
for each department in the employee table. The fullselect in the previous
example uses the result table for DTOTAL to find the department with the
highest total pay. The result table for the entire statement looks similar to
the following results:
DEPTNO
======
D11
Using common table
expressions with views:
You can use common table
expressions before a fullselect in a CREATE VIEW statement. This technique is
useful if you need to use the results of a common table expression in more than
one query.
Example: Using a WITH
clause in a CREATE VIEW statement: The following statement finds the departments that have a
greater-than-average total pay and saves the results as the view RICH_DEPT:
CREATE VIEW RICH_DEPT (deptno)
AS
WITH DTOTAL
(deptno, totalpay) AS
(SELECT
deptno, sum(salary+bonus)
FROM DSN81010.EMP
GROUP BY
deptno)
SELECT deptno
FROM DTOTAL
WHERE totalpay
> (SELECT AVG(totalpay)
FROM DTOTAL);
The fullselect in the
previous example uses the result table for DTOTAL to find the departments that
have a greater-than-average total pay. The result table is saved as the
RICH_DEPT view and looks similar to the following results:
DEPTNO
======
A00
D11
D21
Using common table
expressions when you use INSERT:
You can use common table
expressions before a fullselect in an INSERT statement.
Example: Using a common
table expression in an INSERT statement: The following statement uses the result table for VITALDEPT
to find the manager's number for each department that has a
greater-than-average number of senior engineers. Each manager's number is then
inserted into the vital_mgr table.
INSERT INTO vital_mgr (mgrno)
WITH VITALDEPT
(deptno, se_count) AS
(SELECT
deptno, count(*)
FROM DSN81010.EMP
WHERE job
= 'senior engineer'
GROUP BY deptno)
SELECT d.manager
FROM DSN81010.DEPT
d, VITALDEPT s
WHERE d.deptno =
s.deptno
AND
s.se_count > (SELECT
AVG(se_count)
FROM VITALDEPT);
Creating
recursive SQL by using common table expressions
Queries that use
recursion are useful in applications like bill-of-materials applications,
network planning applications, and reservation systems.
You can use common table expressions to create
recursive SQL If a fullselect of a common table expression contains a reference
to itself in a FROM clause, the common table expression is a recursive
common table expression.
Recursive common table
expressions must follow these rules:
- The first fullselect of the first union (the
initialization fullselect) must not include a reference to the common
table expression.
- Each fullselect that is part of the recursion cycle
must:
- Start with SELECT or SELECT ALL. SELECT DISTINCT is
not allowed.
- Include only one reference to the common table
expression that is part of the recursion cycle in its FROM clause.
- Not include aggregate functions, a GROUP BY clause, or
a HAVING clause.
- The column names must be specified after the table name
of the common table expression.
- The data type, length, and CCSID of each column from
the common table expression must match the data type, length, and CCSID of
each corresponding column in the iterative fullselect.
- If you use the UNION keyword, specify UNION ALL instead
of UNION.
- You cannot specify INTERSECT or EXCEPT.
- Outer joins must not be part of any recursion cycle.
- A subquery must not be part of any recursion cycle.
Important: You should be careful to avoid an infinite
loop when you use a recursive common table expression. DB2® issues a warning if
one of the following items is not found in the iterative
fullselect of a recursive common table expression:
- An integer column that increments by a constant
- A predicate in the WHERE clause
in the form of counter_column < constant or counter_column < :host variable
Examples
of recursive common table expressions
Recursive SQL is very useful in bill of materials (BOM)
applications.
Consider a table of parts with associated
subparts and the quantity of subparts required by each part
For the examples in this
topic, create the following table:
CREATE TABLE PARTLIST (PART VARCHAR(8), SUBPART VARCHAR(8),QUANTITY INTEGER);
Assume that the PARTLIST
table is populated with the values that are in the following table:
Table
1. PARTLIST table
|
||
PART
|
SUBPART
|
QUANTITY
|
00
|
01
|
5
|
00
|
05
|
3
|
01
|
02
|
2
|
01
|
03
|
3
|
01
|
04
|
4
|
01
|
06
|
3
|
02
|
05
|
7
|
02
|
06
|
6
|
03
|
07
|
6
|
04
|
08
|
10
|
04
|
09
|
11
|
05
|
10
|
10
|
05
|
11
|
10
|
06
|
12
|
10
|
06
|
13
|
10
|
07
|
14
|
8
|
07
|
12
|
8
|
Example 1:
Single level explosion:
Single level explosion
answers the question, "What parts are needed to build the part identified
by '01'?". The list will include the direct subparts, subparts of the
subparts and so on. However, if a part is used multiple times, its subparts are
only listed once.
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 RPLORDER BY PART, SUBPART, QUANTITY;
The preceding query
includes a common table expression, identified by the name RPL, that expresses
the recursive part of this query. It illustrates the basic elements of a
recursive common table expression.
The first operand
(fullselect) of the UNION, referred to as the initialization fullselect, gets
the direct subparts of part '01'. The FROM clause of this fullselect refers to
the source table and will never refer to itself (RPL in this case). The result
of this first fullselect goes into the common table expression RPL. As in this
example, the UNION must always be a UNION ALL.
The second operand
(fullselect) of the UNION uses RPL to compute subparts of subparts by using the
FROM clause to refer to the common table expression RPL and the source table
PARTLIST with a join of a part from the source table (child) to a subpart of
the current result contained in RPL (parent). The result goes then back to RPL
again. The second operand of UNION is used repeatedly until no more subparts
exist.
The SELECT DISTINCT in the
main fullselect of this query ensures the same part/subpart is not listed more
than once.
The result of the query is
shown in the following table:
Table
2. Result table for example 1
|
||
PART
|
SUBPART
|
QUANTITY
|
01
|
02
|
2
|
01
|
03
|
3
|
01
|
04
|
4
|
01
|
06
|
3
|
02
|
05
|
7
|
02
|
06
|
6
|
03
|
07
|
6
|
04
|
08
|
10
|
04
|
09
|
11
|
05
|
10
|
10
|
05
|
11
|
10
|
06
|
12
|
10
|
06
|
13
|
10
|
07
|
12
|
8
|
07
|
14
|
8
|
Observe in the result that
part '01' contains subpart '02' which contains subpart '06' and so on. Further,
notice that part '06' is reached twice, once through part '01' directly and
another time through part '02'. In the output, however, the subparts of part
'06' are listed only once (this is the result of using a SELECT DISTINCT).
Remember that with
recursive common table expressions it is possible to introduce an infinite
loop. In this example, an infinite loop would be created if the search
condition of the second operand that joins the parent and child tables was
coded as follows:
WHERE PARENT.SUBPART = CHILD.SUBPART
This infinite loop is
created by not coding what is intended. You should carefully determining what
to code so that there is a definite end of the recursion cycle.
The result produced by
this example could be produced in an application program without using a
recursive common table expression. However, such an application would require
coding a different query for every level of recursion. Furthermore, the
application would need to put all of the results back in the database to order
the final result. This approach complicates the application logic and does not
perform well. The application logic becomes more difficult and inefficient for
other bill of material queries, such as summarized and indented explosion
queries.
Example 2:
Summarized explosion:
A summarized explosion
answers the question, "What is the total quantity of each part required to
build part '01'?" The main difference from a single level explosion is the
need to aggregate the quantities. A single level explosion indicates the
quantity of subparts required for the part whenever it is required. It does not
indicate how many of each subpart is needed to build part '01'.
WITH RPL (PART, SUBPART, QUANTITY) AS ( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY FROM PARTLIST ROOT WHERE ROOT.PART = '01' UNION ALL SELECT PARENT.PART, CHILD.SUBPART, PARENT.QUANTITY*CHILD.QUANTITY FROM RPL PARENT, PARTLIST CHILD WHERE PARENT.SUBPART = CHILD.PART )SELECT PART, SUBPART, SUM(QUANTITY) AS "Total QTY Used" FROM RPL GROUP BY PART, SUBPARTORDER BY PART, SUBPART;
In the preceding query,
the select list of the second operand of the UNION in the recursive common
table expression, identified by the name RPL, shows the aggregation of the
quantity. To determine how many of each subpart is used, the quantity of the
parent is multiplied by the quantity per parent of a child. If a part is used
multiple times in different places, it requires another final aggregation. This
is done by the grouping the parts and subparts in the common table expression RPL
and using the SUM column function in the select list of the main fullselect.
The result of the query is
shown in the following table:
Table
3. Result table for example 2
|
||
PART
|
SUBPART
|
Total QTY Used
|
01
|
02
|
2
|
01
|
03
|
3
|
01
|
04
|
4
|
01
|
05
|
14
|
01
|
06
|
15
|
01
|
07
|
18
|
01
|
08
|
40
|
01
|
09
|
44
|
01
|
10
|
140
|
01
|
11
|
140
|
01
|
12
|
294
|
01
|
13
|
150
|
01
|
14
|
144
|
Consider the total
quantity for subpart '06'. The value of 15 is derived from a quantity of 3
directly for part '01' and a quantity of 6 for part '02' which is needed two
times by part '01'.
Example 3:
Controlling depth:
You can control the depth
of a recursive query to answer the question, "What are the first two
levels of parts that are needed to build part '01'?" For the sake of
clarity in this example, the level of each part is included in the result
table.
WITH RPL (LEVEL, PART, SUBPART, QUANTITY) AS ( SELECT 1, ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY FROM PARTLIST ROOT WHERE ROOT.PART = '01' UNION ALL SELECT PARENT.LEVEL+1, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY FROM RPL PARENT, PARTLIST CHILD WHERE PARENT.SUBPART = CHILD.PART AND PARENT.LEVEL < 2 )SELECT PART, LEVEL, SUBPART, QUANTITYFROM RPL;
This query is similar to
the query in example 1. The column LEVEL is introduced to count the level each
subpart is from the original part. In the initialization fullselect, the value
for the LEVEL column is initialized to 1. In the subsequent fullselect, the
level from the parent table increments by 1. To control the number of levels in
the result, the second fullselect includes the condition that the level of the
parent must be less than 2. This ensures that the second fullselect only
processes children to the second level.
The result of the query is
shown in the following table:
Table
4. Result table for example 3
|
|||
PART
|
LEVEL
|
SUBPART
|
QUANTITY
|
01
|
1
|
02
|
2
|
01
|
1
|
03
|
3
|
01
|
1
|
04
|
4
|
01
|
1
|
06
|
3
|
02
|
2
|
05
|
7
|
02
|
2
|
06
|
6
|
03
|
2
|
07
|
6
|
04
|
2
|
08
|
10
|
04
|
2
|
09
|
11
|
06
|
2
|
12
|
10
|
06
|
2
|
13
|
10
|
