Nested and Common Table Expressions

A nested table expression, a special kind of subquery, is used in the FROM clause of an SQL statement. Nested table expressions create logical temporary tables that are known only in the SQL statement that defines them. They may or may not result in the creation of a physically materialized local table, as the SQL may be merged with the outer SQL statement.

These subqueries can be considered temporary views and are also sometimes referred to as inline views. You can use nested table expressions to select from a grouped table or to obtain the same results that you expect from a view.

Consider the problem of obtaining the maximum average score for the DB2 Certification program exams. To gather this result, you must first obtain the averages and then select the maximum value from that list. The following example uses a nested table expression to accomplish this request:

 SELECT MAX(AVG_SCORE) FROM (    SELECT NUMBER,    AVG(SCORE) AS AVG_SCORE    FROM TEST_TAKEN    GROUP BY NUMBER) AS AVERAGES 

In this example, the nested subselect creates a temporary table that will be used by the outer SELECT to obtain the maximum average score. This temporary table is called AVERAGES.

Although included in the nested table expression, the number column is not required to be able to gather the average for each one of the exams, as it is not referenced in the outer query. After the subquery is completed, the outer SELECT will be able to obtain the maximum value of the averages calculated in the nested table expression with or without the number column present in the nested table expression.

An advantage of using nested table expressions rather than views is that nested table expressions exist only during the execution of the query, so you don't have to worry about their maintenance. Nested table expressions reduce contention over the system catalog tables. Because they are created at execution time, they can be defined using host variables. Nested table expressions also give the SQL programmer more information about the SQL statement. Views are hidden, so it is possible to introduce redundancy if the programmer is not aware of the full view description.

NOTE

The TABLE clause can also be used to denote that the subquery following it is a nested table expression. This keyword is mandatory if the nested table expression contains a correlated reference to a prior FROM clause table. The TABLE keyword is also used to denote a user-defined TABLE function. Refer to Chapter 15 for more information on UDFs.

Version 8 of DB2 has support for common table expressions. These expressions can provide performance improvements by computing a value once, not multiple times, during the execution of a query. Common table expressions can be used in SELECT, CREATE VIEW, and INSERT statements and may also contain references to host variables. An example of a common table expression follows:

 WITH DEPTOTAL (DEPT_NO, TOTSALARY) AS SELECT WORKDEPT, SUM (SALARY+COMM)    FROM EMP    GROUP BY WORKDEPT SELECT DEPT_NO FROM DEPTOTAL WHERE TOTSALARY =    (SELECT MAX(TOTSALARY)            FROM DEPTOTAL) 

In this example, the DEPTOTAL is established by calculating the sum, or the salary plus commissions, for each department. This data in the DEPTOTAL common table expression can then be used in the remainder of the query.