EXISTS TABLE: nested iteration

This SHOWPLAN step is very similar to the Nested iteration method. The difference, however, is that this step indicates a nested iteration on a table that is part of an existence test in a query. An existence test can be written in several ways using Transact-SQL statements, such as EXISTS, IN, or =, <, >, <=, >=, <> ANY.

The following examples demonstrate the SHOWPLAN output with queries that test for 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
FROM TABLE
publishers
EXISTS TABLE : 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
FROM TABLE
publishers
EXISTS TABLE : nested iteration
Table Scan