Naomi,
There are a lot of variables that drive query plans, what works well for a smallish lookup table isn't the same for a large transaction table. And query plans only show one very narrow view of what's going on inside the engine. You really need to look at the I/Os that happen to verify that excessive page reads are not happening.
For me now though the rule is to use this form of EXISTS check
where exists ( select 'x' from ... )
Also FWIW I've found that SQL2005 doesn't do near as good a job as SQL2000 on some query plans. I've found that hints on some joins and where clauses have
radically improved performance in 2005 where they were not needed in 2000.
>See also
http://www.zimbio.com/SQL/articles/589/SQL+SERVER+SELECT+1+vs+SELECT+Interesting>
>Not every one agrees.
>
>
http://forums.asp.net/t/1374663.aspx