In some cases, a subquery is processed in two steps: the results from the inner query are materialized, or stored in a temporary worktable or internal variable, before the outer query is executed. The subquery is executed in one step, and the results of this execution are stored and then used in a second step. Adaptive Server materializes these types of subqueries:
Noncorrelated expression subqueries
Quantified predicate subqueries containing aggregates where the having clause includes the correlation condition
