· Do not use:
column_list < ( count(*) table2 ..)
column_list ( * table2 ...)
· When you use , SQL Server does not know that you are doing an existence check. It counts all matching values, either by doing a table scan or by scanning the smallest non-clustered index.
· When you use , SQL Server knows you are doing an existence check. When it finds the first matching value, it returns and stops looking. The same applies to using instead of or.