Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
Well, yes, but why they yield the same execution plan? – jrara Aug 26 '12 at 10:04
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. – a_horse_with_no_name Aug 26 '12 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 '12 at 11:20
@Aftab Please don't post any more comments as answers. We have a comment function for a reason. – JNK Aug 26 '12 at 12:17
@Aftab: This question is tagged with [sql-server], not [mysql]. – ypercubeᵀᴹ Aug 26 '12 at 12:33
up vote 11 down vote accepted

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...

share|improve this answer

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.)

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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