Quantified predicate subqueries containing aggregates

Some subqueries that contain vector (grouped) aggregates can be materialized. These are:

The materialization of the subquery results in these two steps:

Materialization saves the cost of evaluating the aggregates once for each row in the table. For example, this query:

select title_id
from titles
where total_sales in (select max(total_sales)
                     from titles
                     group by type)

Executes in these steps:

select maxsales = max(total_sales)
    into #work
    from titles
    group by type
select title_id
    from titles, #work
    where total_sales = maxsales

The total cost of executing quantified predicate subqueries is the sum of the query costs for the two steps.

When there are where clauses in addition to a subquery, Adaptive Server executes the subquery or subqueries last to avoid unnecessary executions of the subqueries. Depending on the clauses in the query, it is often possible to avoid executing the subquery because less expensive clauses can determine whether the row is to be returned:

In both cases, as soon as the status of the row is determined by the evaluation of one clause, no other clauses need to be applied to that row. This provides a performance improvement, because expensive subqueries need to be executed less often.