Microsoft® SQL Server 2012 Unleashed

Subquery Processing

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 IN, = 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 ...