Here's the two showplans. The first uses the subquery in the column list, the second uses the subquery as a predicate in the WHERE clause:

  |--Nested Loops(Left Semi Join, OUTER REFERENCES:([o].[CustomerID], [o].[OrderID]))
       |--Index Scan(OBJECT:([Northwind].[dbo].[Orders].[CustomerID] AS [o]))
       |--Filter(WHERE:([o].[OrderID]=[o1].[OrderID]))
            |--Top(1)
                 |--Filter(WHERE:([o1].[CustomerID]=[o].[CustomerID]))
                      |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([Northwind].[dbo].[Orders] AS [o1]))
                           |--Index Scan(OBJECT:([Northwind].[dbo].[Orders].[OrderDate] AS [o1]), ORDERED FORWARD)

  |--Compute Scalar(DEFINE:([OSub].[OrderID]=[OSub].[OrderID]))
       |--Nested Loops(Left Outer Join, OUTER REFERENCES:([O].[CustomerID]))
            |--Stream Aggregate(GROUP BY:([O].[CustomerID]))
            |    |--Index Scan(OBJECT:([Northwind].[dbo].[Orders].[CustomerID] AS [O]), ORDERED FORWARD)
            |--Compute Scalar(DEFINE:([OSub].[OrderID]=[OSub].[OrderID]))
                 |--Top(1)
                      |--Filter(WHERE:([OSub].[CustomerID]=[O].[CustomerID]))
                           |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([Northwind].[dbo].[Orders] AS [OSub]))
                                |--Index Scan(OBJECT:([Northwind].[dbo].[Orders].[OrderDate] AS [OSub]), ORDERED FORWARD)

There's a HUGE difference in the performance of these two queries. Notice that the totalSubTree costs are very different: 2.122 vs. 0.359.

I think the key is the the GROUP BY. I believe that the optimizer notices that the group by will filter down the list, which can be done before the subquery is execute. IOW, the subquery only has to run agains 89 rows instead of the entire table.

With the query that I wrote, each row in the outer table must be compared to the results of the subquery in order to determine a match. There no way to pare it down since the subquery is correlated.

Isn't query optimization fun?

-Mike