should i prefer join over exists or in

Question

hi,

I have seen people use exists when exists is required and no cols are required in select.

But I notice sometimes exists slows down and join performs better

q1) is there any news i should know ,  that one should not use exists or in , and should go for  join etc etc

 

yours sincerely

Answer 1

There is no simple answer to that.

However, in general, I find that the SQL Server optimizer does a much better job of optimizing EXISTS clauses than the equivalent joins.

Also, in the end, an EXISTS clause is often much clearer for someone else reading the code, as it generally shows the intent of the code better. Finally, it often keeps the existence test within a single predicate, which again avoids confusing the rest of the query with the existence aspects.