Query Specifications, Subqueries, Query Expressions, and Cursor Specifications › Subqueries

Subqueries

A subquery is a query specification used in predicates or in the SET clause of the UPDATE statement or in XML-value-expression. Each subquery in a predicate represents a set of zero or more values to be used in the test specified by the predicate. A subquery used in the SET clause of the UPDATE statement or in XML-value-expression represents either the NULL value or a single value. The values represented by a subquery are derived from the query specification of the subquery.

Subqueries are always enclosed in parentheses, except when used as XML-value-expression

Nesting Subqueries

You can nest subqueries in an SQL statement. For example, a subquery in the WHERE parameter of a SELECT statement can include another subquery in its own WHERE or HAVING parameter:

select ... where ... (select ... where ... (select ...) );
                                           └─────┬────┘
                                             Subquery 2
                     └─────────────────┬────────────────┘
                                   Subquery 1
└────────────────────────────┬───────────────────────────┘
                     SELECT statement

Outer References

An outer reference is a reference to a column named in an outer subquery, an outer query specification, or the SELECT statement where the subquery occurs.

For example, with reference to the illustration above:

• If Subquery 2 contains a reference to a column named in Subquery 1, it is an outer reference
• If Subquery 2 contains a reference to a column named in the SELECT statement, it is an outer reference
• If Subquery 1 contains a reference to a column named in the SELECT statement, it is an outer reference

Correlated Subqueries

A correlated subquery is a subquery that contains an outer reference.

CA IDMS must evaluate a correlated subquery once for each value in the outer-reference column. The result of the evaluation differs depending on the value in the outer-reference column.

In contrast, CA IDMS must evaluate a subquery that does not include any outer references only once.

For an example of a correlated subquery, see A Correlated Subquery in a Comparison Predicate.