SQL Select IN versus LIKE in WHERE clause

Which one 开发者_如何学JAVAis faster in performance?

SELECT field1, field2, field3, field4
FROM MyTable
WHERE field5 LIKE '%AL'

versus

SELECT field1, field2, field3, field4
FROM MyTable
WHERE field5 IN ('AAL', 'IAL')

or it doesn't make any difference?

Your mileage may vary, but the second one should be faster, because it is two index-backed lookups, versus a full index scan. Without an index on field5 it should not matter (full table scan in both cases).

If you don't have a covering index (or at least an index on field5) then both will require table scans so will be equally poor.

About the queries... The second one is the same as WHERE field5 = 'AAL' OR field5 = 'IAL' which is 2 precise values to look for (eg a seek is likely). The LIKE and leading wildcard implies "I don't know how many values to look for" seek will never happen

About indexes... If you do have an index on field5 only, then the second one will probably have 2 key lookups to get the rest of the data. The first will probably ignore this index because it has a leading wildcard. So the 2nd is better assuming things behave as I'd expect.

With a covering index, then the 2nd one again bit no key lookup

About search arguments... If you change the IN to variables then the plan will change again. Queries with constants are quicker then queries with variables because with constants the values are known up front.

But, have you tried it...

I would definitely test on your platform. Some RDBMS are horrible at IN logic, i.e. much slower than you'd expect. Mysql has this issue, you don't specify in your question.

as other stated depends on your choice of RDBMS, if you are using MS SQL, both statements are identical in terms of performance

UPDATE: As per Martin comment, the above is true when there are no indexes present for field5, which was the original assumption for the question.

IN is faster than the LIKE command...