Pitfalls of correlated sub-queries

It is not uncommon to use sub-queries to express certain predicates inline in queries, but developers must keep in mind that joins are frequently better than correlated sub-queries.

The following query examples can be executed in the scope of the AdventureWorks sample database:

SELECT wo.StockedQty, wo.WorkOrderID, wor.ActualCost
FROM Production.WorkOrder AS wo
INNER JOIN Production.WorkOrderRouting AS wor ON wo.WorkOrderID = wor.WorkOrderID
WHERE wor.WorkOrderID = 12345;

SELECT wo.StockedQty, wo.WorkOrderID,
       (SELECT wor.ActualCost
              FROM Production.WorkOrderRouting AS wor
              WHERE wor.WorkOrderID = 12345)
FROM Production.WorkOrder AS wo
WHERE wo.WorkOrderID IN
       (SELECT wor.WorkOrderID
              FROM Production.WorkOrderRouting AS wor
              WHERE wor.WorkOrderID = 12345);

These yield different query plans but...