This statement appears in the SHOWPLAN output for a query that involves a FULL OUTER JOIN. In queries that involve FULL OUTER JOIN, the optimizer will always choose to insert the rows from one of tables in the full outer join into a worktable. (This is similar to the reformatting strategy that is used to join tables.) The optimizer then modifies the full outer join so that the worktable is the right table of the full outer join.
The FULL OUTER JOIN: nested iteration method is two-phase technique. In the first phase, SQL Server will loop over the plan for the right table for each row in the left table and will return all rows that meet the search condition that is specified for the join. SQL Server also marks each of these rows by updating a column in the row that is added to the worktable for this purpose. If no rows meet the search condition, a single row with all columns set to NULL is returned for the right table.
In the second phase, after all the rows in the left table have been processed, SQL Server loops over the plan for the right table again, and returns all rows in the right table that were not returned in the first phase and a row from the left table with all columns set to NULL.
The plan for the right table will immediately follow the FULL OUTER JOIN step and is indented to isolate the steps for the right table from the steps for the outer query.
The following example demonstrates the SHOWPLAN output for a query that involves FULL OUTER JOIN:
Query: SELECT job_desc, lname
FROM jobs j FULL JOIN employee e ON j.job_id = e.job_id
SHOWPLAN: STEP 1
The type of query is INSERT
The update mode is direct
Worktable created for REFORMATTING
FROM TABLE
jobs
Nested iteration
Table Scan
TO TABLE
Worktable 1
STEP 2
The type of query is SELECT
FROM TABLE
employee e
Nested iteration
Table Scan
FULL OUTER JOIN : nested iteration
FROM TABLE
Worktable 1
Nested iteration
Using Clustered Index