Prior to DB2 V4.1, the FROM clause of a SELECT statement could reference only table names and view names.

Now, the FROM clause can reference a subselect statement. The subselect, enclosed in parentheses, is called a nested table expression. A correlation name (a name for the table resulting from the nested table expression) must be specified when a nested table expression is used.

Nested table expressions are useful, or necessary, in these cases:

• If you want to GROUP BY the results of a function (such as SUM, AVG, CHAR, SUBSTR, etc.)
• When coding a 3+ way Outer Join (discussed in subsequent pages).
• To avoid holding locks for extended periods of time when joining a small table to a large one.

Example:

Result: