Quantified predicate subqueries can be executed as normal joins when the result set of the subquery is a set of unique values. For example, if there is a unique index on publishers.pub_id, this single-table subquery is guaranteed to return a set of unique values:
select title
from titles
where pub_id in (select pub_id
from publishers
where state = "TX")
With a nonunique index on publishers.city, this query can also be executed using a regular join:
select au_lname
from authors a
where exists (select city
from publishers p where p.city = a.city)
Although the index on publishers.city is not unique, the join can still be flattened to a normal join if the index is used to filter duplicate rows from the query.
When a subquery is flattened to a normal join, showplan output shows a normal join. If filtering is used, showplan output is not different; the only diagnostic message is in dbcc traceon(310) output, where the method for the table indicates “NESTED ITERATION with Tuple Filtering.”
