Subqueries

A subquery is a query specification used in predicates or in an 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 an 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.
idmscu
A subquery is a query specification used in predicates or in an 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 an 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 Expansion of Subquery.