Nested Table Expressions

Team-Fly

---

 

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents
	Chapter 4.  Advanced SQL Coding

---

Nested Table Expressions

A nested table expression is a special kind of subquery. This subquery is used in the FROM clause of a 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 a physically materialized local table being created, 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. In the following example, we use 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 the number column is included in the nested table expression, it is not actually required to be able to gather the average for each one of the exams, since 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 over views is that nested table expressions exist only during the execution of the query, so you don't have to worry about their maintenance. They reduce contention over the system catalog tables, and since they are created at execution time, they can be defined using host variables . They also give the SQL programmer more information about the SQL statement. Since views are hidden, it is possible that redundancy is introduced 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, "Object Relational Programming" for more information on UDFs.

---

Team-Fly

Top