LIKE clause vs. IN clause--which is better?

Question

Which one is better using condition in where statement or in join statement? Can anyone please help?

Answer 1

Here's a good reference:

http://msdn.microsoft.com/en-us/library/aa933232%28SQL.80%29.aspx

Answer 2

If the requirement to to filter the rows by a known set of fixed values then where x in (a, b, c, ...) will work better than where x like 'g' because it may not be possible to create a suitable like clause.

If the requirement is to filter by a prefix then where x like 'prefix%' will work better than where x in (a, b, c, ...) because it might not be possible to create an in() clause that is correct.

Answer 3

Any condition that prevents the a SQL server optimizer to use an index it's bad condition.

If in your case in or like operator is better that says the query plan only.

Typically a "%" wildcard at the beginning of the string prevents the SQL Server to use an index. Sometimes a in operator prevents to use an index.