AND NOT EXISTS : nested iteration

This SHOWPLAN step indicates a nested iteration on a subquery that is part of a non-existence test in a query. When the result of the subquery is true, subsequent parts of the plan are not evaluated. 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 examples demonstrate the SHOWPLAN output with queries that test for the non-existence of values:

Query 1:

SELECT au_lname, au_fname
FROM authors
WHERE NOT EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)

SHOWPLAN 1:

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

Query 2:

SELECT title
FROM titles
WHERE pub_id NOT IN
(SELECT pub_id
FROM publishers
WHERE city LIKE 'B%')

SHOWPLAN 2:

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