Flattened subqueries executed as regular joins

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.”