Flattened subqueries using duplicate elimination

When it is cheaper to place the subquery tables as outer tables in the join order, the query is executed by:

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:

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.