Evaluate Grouped ANY AGGREGATE.
Evaluate Ungrouped ANY AGGREGATE.
All quantified predicate subqueries that are not flattened use the internal ANY aggregate. Do not confuse this with the any predicate that is part of SQL syntax.
The subquery returns TRUE when a row from the subquery satisfies the conditions of the subquery predicate. It returns FALSE to indicate that no row from the subquery matches the conditions.
For example:
select type, title_id
from titles
where price > all
(select price
from titles
where advance < 15000)
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE
titles
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Run subquery 1 (at nesting level 1).
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.
QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 4).
Correlated Subquery.
Subquery under an ALL predicate.
STEP 1
The type of query is SELECT.
Evaluate Ungrouped ANY AGGREGATE.
FROM TABLE
titles
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
END OF QUERY PLAN FOR SUBQUERY 1.
