SQL Server › Subqueries

How would you rewrite a slow correlated subquery as an equivalent, potentially faster JOIN based query?

Advanced

Answer

You identify the relationship being checked by the correlated subquery, and express that same logic using a JOIN combined with either GROUP BY for aggregation needs or DISTINCT for simple existence checks, which often allows SQL Server's optimizer to process the entire operation as a single efficient set based operation instead of repeatedly executing the subquery for each outer row.

-- Correlated subquery approach
SELECT c.Name, (SELECT COUNT(*) FROM Orders o WHERE o.CustomerId = c.CustomerId) AS OrderCount FROM Customers c;

-- Equivalent JOIN based approach
SELECT c.Name, COUNT(o.OrderId) AS OrderCount
FROM Customers c LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
GROUP BY c.Name;

Real-world example A team rewrites a slow report that used a correlated subquery to count each customer's orders into an equivalent LEFT JOIN with GROUP BY, cutting the report's execution time significantly on their large customer table.

Common follow-ups: Does SQL Server's optimizer sometimes automatically rewrite subqueries into joins internally?;How do you verify that the JOIN based rewrite actually produces identical results to the original subquery?

Joins;Query Optimization & Plans