LEFT OUTER JOIN: nested iteration

This statement appears in the SHOWPLAN output for a query that includes a LEFT OUTER JOIN. The LEFT OUTER JOIN: nested iteration method is the technique used to perform left outer joins. (The optimizer always converts right outer joins into left outer joins.) LEFT OUTER JOIN indicates that 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. If no rows meet the search condition, a single row with all columns set to NULL is returned for the right table. The plan for the right table will immediately follow the LEFT 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 a left outer join:

Query:    SELECT job_desc, lname
          FROM jobs j LEFT JOIN employee e ON j.job_id = e.job_id
  
SHOWPLAN: STEP 1
          The type of query is SELECT
          FROM TABLE
          jobs j
          Nested iteration
          Table Scan
          LEFT OUTER JOIN : nested iteration
              FROM TABLE
              employee e
              Nested iteration
              Table Scan