I have a correlated subquery like this (from BOL):

SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID 
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID 
WHERE 5000.00 IN
    (SELECT Bonus
    FROM Sales.SalesPerson sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;

When I rewrite this query using joins

select c.LastName, c.FirstName, e.BusinessEntityID, d.Bonus
from Person.Person as c 
    inner join HumanResources.Employee as e on e.BusinessEntityID = c.BusinessEntityID
    inner join Sales.SalesPerson as d on d.BusinessEntityID = c.BusinessEntityID
where Bonus = 5000.00

And look the actual execution plan, it looks exactly the same in both queries. Why? I was thinking that correlated subquery is much slower because of the nested loop and the execution plan looks different? Is it because there is not much data in these tables?

  • Well, yes, but why they yield the same execution plan?
    – jrara
    Aug 26, 2012 at 10:04
  • 2
    Because the optimizer is smart enough to detect the situation. It's not uncommon that (co-related) sub-selects and joins yield the same plan if possible.
    – user1822
    Aug 26, 2012 at 10:44
  • Please provide EXPLAIN output of original and rewrite SQLs. Also, provide SHOW CREATE TABLE and SHOW TABLE STATUS info for each table
    – user11123
    Aug 26, 2012 at 11:20
  • @Aftab Please don't post any more comments as answers. We have a comment function for a reason.
    – JNK
    Aug 26, 2012 at 12:17
  • 2
    @Aftab: This question is tagged with [sql-server], not [mysql]. Aug 26, 2012 at 12:33

2 Answers 2


The two queries are logically identical and do produce the same plan. The simplification phase of the Query Optimizer handles this.

They're identical because of the constraints that are on the tables - foreign keys, uniqueness, nullability...


A good optimizer will recognize when a correlated query could have been written as a join and use the same plan. Many correlated queries could be just as easily written as a join. In this case the optimizer is doing its job. Optimizing the query in this manner allows plans which would not make sense using the correlated query.

I tend to write a correlated query when it makes sense semantically, and use a join when that is appropriate semantically. Modern optimizers will generate the appropriate query. As optimizers improve, I then to write queries for understandability, and not to try to second guess the optimizer. (I find the optimizer usually does better than I do anyway. There are rare cases where I know something that the optimizer can't determine from the query.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.