All 7 Subqueries questions
More in Subqueries
What is the difference between using EXISTS and IN when checking for the presence of related data with a subquery? How do you use a subquery within the SELECT list to calculate a related aggregate value alongside each row of the outer query? What are common mistakes developers make when writing subqueries that can lead to incorrect results or poor performance? How would you rewrite a slow correlated subquery as an equivalent, potentially faster JOIN based query?
Advertisement
SQL Server › Subqueries

How does the SQL Server query optimizer typically handle and potentially transform a subquery internally when generating an execution plan?

Advanced

Answer

The optimizer often transforms certain types of subqueries internally into equivalent join operations behind the scenes, especially simple non correlated ones, meaning the way you write a query does not always dictate exactly how it will be physically executed, though complex or correlated subqueries sometimes limit the optimizer's ability to make these kinds of beneficial transformations.
-- Examine the actual execution plan to see how
-- a subquery was physically implemented
SET STATISTICS XML ON;
SELECT * FROM Customers WHERE CustomerId IN (SELECT CustomerId FROM Orders);
Real-world example A database administrator explains to a junior developer that a subquery they wrote was automatically converted into a join internally by the optimizer, which is why its performance was actually just as good as writing the join explicitly by hand.

Common follow-ups: How can you tell from an execution plan whether a subquery was transformed into a join?;Are there subquery patterns that specifically prevent this kind of optimizer transformation?

Query Optimization & Plans;Joins
All 7 Subqueries questions