• Can anyone explain, in detail, why the outer join solution is faster than the Subquery solution in this example?  Is the Exists method fastest of all?

    It depends...  One could be faster than another in different situations.  It all boils down to which query plan SQL Server decides to use for a given query and a given set of data.  As a side not, on SQL 2000 EXISTS tends to be faster than the IN operator.  The explanation I've seen for this is that the IN operator needs to serialize the entire subquery before it can perform the comparison whereas the EXISTS operator does not.  This behavior may be changed in SQL 2005, although I can't test it myself right now.

    I've examined all three on a limited number of rows and the performance and the plans are identical.  Why do you reckon one will be faster than the next with table growth?

    Basically it's all up to the optimizer.  It takes a lot of factors into consideration, including amount of data, when generating query plans.  Changing the amount of data could change the query plan it chooses.

    Correct me if I'm wrong, but when you do a left outer join the left part of the join has a full table scan.  How is this faster than a subquery based on an index compared to another column based upon an index?  Also, I can't see the difference in using EXISTS over a standard subquery as it still is effectively comparing values inside the EXISTS subquery?

    Not necessarily a "full table scan", if it's properly indexed.  You could end up with an (clustered) index scan, which would be more efficient in most cases.  The rationale against the subquery versus EXISTS might be the same as for EXISTS versus IN.  If SQL Server has to serialize the entire subquery before using it, it could be less efficient than using EXISTS.