There are several ways you can write queries that perform an existence test, for example, using exists, in, or =any. These queries are treated as if they were written with an exists clause. The following example shows an existence test. This query cannot be flattened because the outer query contains or:
select au_lname, au_fname
from authors
where exists
(select *
from publishers
where authors.city = publishers.city)
or city = "New York"
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE
authors
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 EXISTS predicate.
STEP 1
The type of query is SELECT.
Evaluate Ungrouped ANY AGGREGATE.
FROM TABLE
publishers
EXISTS TABLE : 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.
