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 the NOT EXISTS 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
            NOT EXISTS : nested iteration
              FROM TABLE
              publishers
              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
            NOT EXISTS : nested iteration
              FROM TABLE
              publishers
              Nested iteration
              Table Scan