OR EXISTS : nested iteration

This SHOWPLAN step indicates a nested iteration on a subquery that is part of an existence test in an OR clause. When the result of the subquery is true, subsequent parts of the plan are not evaluated and the result of the plan for the OR clause is true. An existence test can be written in several ways using Transact-SQL statements, such as EXISTS, IN, <> ANY, = ANY, < ANY, <=ANY, > ANY, or >= ANY. The plan for the subquery will immediately follow this step, and is indented to isolate the steps for the subquery from the steps for the outer query.

The following example demonstrates the SHOWPLAN output for queries with OR clauses that test for the existence of values:

Query:

SELECT title
FROM titles
WHERE advance > ANY
(SELECT advance
FROM publishers, titles
WHERE titles.pub_id = publishers.pub_id
AND pub_name = 'Algodata Infosystems')
OR pub_id IN
(SELECT pub_id
FROM publishers
WHERE city NOT LIKE 'B%')

SHOWPLAN:

STEP 1
The type of query is SELECT
FROM TABLE
titles
Nested iteration
Table Scan
AND EXISTS : nested iteration
OR EXISTS : nested iteration
FROM TABLE
publishers
EXISTS TABLE : nested iteration
Table Scan
FROM TABLE
titles
EXISTS TABLE : nested iteration
Table Scan
OR EXISTS : nested iteration
FROM TABLE
publishers
EXISTS TABLE : nested iteration
Table Scan