Correlated subquery and join: still the same exectution plan?

Question

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) ;
GO

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?

Answer 1

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

Answer 2

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