EXISTS table: nested iteration

This SHOWPLAN step indicates a nested iteration on a subquery that is part of an existence test in a query. When the result of the subquery is false, subsequent parts of the plan are not evaluated. An existence test can be written in several ways by using Transact-SQL statements, such as EXISTS, IN, <> ANY, = ANY, < ANY, <=ANY, > ANY, or >= ANY. The plan for the subquery will immediately follow the EXISTS TABLE step, and it 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 existence of values:

Query 1:        SELECT au_lname, au_fname
            FROM authors
            WHERE 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
            EXISTS : nested iteration
              FROM TABLE
              publishers
              Nested iteration
              Table Scan
  
Query 2:        SELECT title
            FROM titles
            WHERE pub_id 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
            EXISTS : nested iteration
              FROM TABLE
              publishers
              Nested iteration
              Table Scan