This SHOWPLAN step indicates a nested iteration on a subquery that is part of an 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. An existence test can be written in several ways using Transact-SQL statements, such as EXISTS, IN, <> ANY, = ANY, < ANY, <=ANY, > ANY, or >= ANY. 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 existence of values:
SELECT title FROM titles WHERE advance > ANY (SELECT advance FROM publishers, titles WHERE titles.pub_id = publishers.pub_id AND pub_name = 'Algodata Infosystems') OR pub_id IN (SELECT pub_id FROM publishers WHERE city NOT LIKE 'B%')
STEP 1 The type of query is SELECT FROM TABLE titles Nested iteration Table Scan AND EXISTS : nested iteration OR EXISTS : nested iteration FROM TABLE publishers EXISTS TABLE : nested iteration Table Scan FROM TABLE titles EXISTS TABLE : nested iteration Table Scan OR EXISTS : nested iteration FROM TABLE publishers EXISTS TABLE : nested iteration Table Scan