When it is cheaper to place the subquery tables as outer tables in the join order, the query is executed by:
Performing a regular join with the subquery flattened into the outer query, placing results in a worktable.
Sorting the worktable to remove duplicates.
For example, salesdetail has duplicate values for title_id, and it is used in this subquery:
select title_id, au_id, au_ord
from titleauthor ta
where title_id in (select ta.title_id
from titles t, salesdetail sd
where t.title_id = sd.title_id
and ta.title_id = t.title_id
and type = ’travel’ and qty > 10)
If the best join order for this query is salesdetail, titles, titleauthor, the optimal join order can be used by:
Selecting all of the query results into a worktable
Removing the duplicates from the worktable and returning the results to the user
showplan Messages for Flattened Subqueries Performing Sorts
showplan output includes two steps for subqueries that use normal joins plus a sort. The first step shows “Worktable1 created for DISTINCT” and the flattened join. The second step shows the sort and select from the worktable.
dbcc traceon(310) prints a message for each join permutation when a table or tables from a quantified predicate subquery is placed first in the join order. Here is the output when the join order used for the query above is considered:
2 - 0 - 1 - This join order created while converting an exists join to a regular join, which can happen for subqueries, referential integrity, and select distinct.
