Evaluate Ungrouped ONCE AGGREGATE.
Evaluate Grouped ONCE AGGREGATE.
Expression subqueries return only a single value. The internal ONCE aggregate checks for the single result required by an expression subquery.
This query returns one row for each title that matches the like condition:
select title_id, (select city + " " + state
from publishers
where pub_id = t.pub_id)
from titles t
where title like "Computer%"
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE
titles
t
Nested iteration.
Index : title_ix
Forward scan.
Positioning by key.
Keys are:
title ASC
Run subquery 1 (at nesting level 1).
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 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 1).
Correlated Subquery.
Subquery under an EXPRESSION predicate.
STEP 1
The type of query is SELECT.
Evaluate Ungrouped ONCE AGGREGATE.
FROM TABLE
publishers
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
END OF QUERY PLAN FOR SUBQUERY 1.
