SQL Server optimizes subqueries differently, depending on how they are written. For example, SQL Server attempts to flatten some subqueries into joins when possible, to allow the Query Optimizer to select the optimal join order rather than be forced to process the query inside-out. The following sections examine the different types of subqueries and how SQL Server optimizes them.
IN, ANY, and EXISTS Subqueries
In SQL Server, any query that contains a subquery introduced with an
= ANY, or
EXISTS predicate is usually flattened into an existence join unless the outer query also contains an
OR clause or unless the subquery is correlated or contains one or more aggregates.
An existence join is optimized the same way as a ...