Subqueries are resolved in several ways in Adaptive Server, and the abstract plans reflect the query execution steps:
Materialization – The subquery is executed and results are stored in a worktable or internal variable. See “Materialized subqueries”.
Flattening – The query is flattened into a join with the tables in the main query. See “Flattened subqueries”.
Nesting – The subquery is executed once for each outer query row. See “Nested subqueries”.
Abstract plans do not allow the choice of the basic subquery resolution method. This is a rule-based decision and cannot be changed during query optimization. Abstract plans, however, can be used to influence the plans for the outer and inner queries. In nested subqueries, abstract plans can also be used to choose where the subquery is nested in the outer query.
