Execution Plans - Take #2

However, populate the tables with more than a few sample rows of data and the same rough comparison produces a different result. Option #2 (CTE) still seems likely to be the least expensive query (9%), but Option #3 (APPLY) is now the most expensive (76%). You can see the majority of that cost is the index spool used due to the APPLY.

New Sample Size

New Execution Plans

With the increased amount of data,

While option #2 would seem likely to be most efficient overall, there are a lot of factors involved (indexes, data volume, statistics, version, etc), so you should examine the actual execution plans in your own environment. As with most things, the answer to the question "which is best" is: it depends.