Noncorrelated expression subqueries

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:

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.