Noncorrelated expression subqueries must return a single value. When a subquery is not correlated, it returns the same value, regardless of the row being processed in the outer query. The query is executed by:
Executing the subquery and storing the result in an internal variable.
Substituting the result value for the subquery in the outer query.
The following query contains a noncorrelated expression subquery:
select title_id
from titles
where total_sales = (select max(total_sales)
from ts_temp)
Adaptive Server transforms the query to:
select <internal_variable> = max(total_sales)
from ts_temp
select title_id
from titles
where total_sales = <internal_variable>
The search clause in the second step of this transformation can be optimized. If there is an index on total_sales, the query can use it. The total cost of a materialized expression subquery is the sum of the cost of the two separate queries.
