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?

The Title and the question seems different. Can you please take a look? — Deviprasad Das, Aug 02 '10 at 05:26
If going off the title, this is a duplicate: http://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql — OMG Ponies, Aug 02 '10 at 05:30

score 1 · Accepted Answer · answered Aug 02 '10 at 05:26

1

Here's a good reference:

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

answered Aug 02 '10 at 05:26

naikus

24,302
4
42
43

score 1 · Answer 2 · answered Aug 02 '10 at 15:03

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.

score 0 · Answer 3 · answered Aug 02 '10 at 13:54

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.

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

3 Answers3