Just to add a bit to this discussion.

At present, if you need to check multiple conditions in several tables, put the table with lesser records on top in your query.

See this blog post http://blogs.msdn.com/conor_cunningham_msft/archive/2010/05/14/conor-vs-anti-semi-join-reordering.aspx and the following thread that originated it

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/278c272b-5ebb-4fda-8985-49927bbd3799


>>The whole purpose of IN is to check one value against another. Same case with EXISTS.
>>
>>The way you wrote it is essentially the same as also do an INNER JOIN. I think we may better use EXISTS to avoid any ambiguity.
>
>No. INNER JOIN would select more than 1 record, in case of one-to-many. And forget about EXISTS for now. I am just trying to see why IN does not need the T1.PK_FLD = T2.PK_FLD in the subquery. For example, I used in my example columns that have the same name but there could be a case where columns have different names. For example:
>
>

>select * from Table1 T! where COL1 in (select COL2 from Table2 T2 where COL3='123')
>

>
>Would in the above case you still not use the T1.PK_FLD = T2.PK_FLD?