OR NOT EXISTS : nested iteration

This SHOWPLAN step indicates a nested iteration on a subquery that is part of a non-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. A non-existence test can be written in several ways using Transact-SQL statements, such as NOT EXISTS, NOT IN, <> ALL, = ALL, < ALL, <=ALL, > ALL, or >= ALL. 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 non-existence of values:

Query:

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

SHOWPLAN:

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