SQL difference between IN and OR in WHERE

Question

What is the difference between the following two commands?

SELECT * FROM table WHERE id IN (id1, id2, ..., idn)

and

SELECT * FROM table WHERE id = id1 OR id = id2 OR ... OR id = idn

Which one is faster? And will it be different if id is another type?

Answer 1

They are semantically identical.

IN is just a shorthand for a string of equality statements like in your second example. Performance should also be identical.

The type shouldn't matter, it will always evaluate to a string of equalities.

There is a difference when you are using NOT IN and data that can be NULL, though - a NULL will not evaluate false for a NOT IN comparison, so you may get rows you didn't expect in the result set.

As an example:

SELECT 'Passed!' WHERE NULL NOT IN ('foo', 'bar')

The above query will not return a row even though at face value NULL is neither 'foo' or 'bar' - this is because NULL is an unknown state, and SQL cannot say with certainty that the unknown value is NOT one of the IN listed values.

Answer 2

This depends on specific DBMS optimizer implementation and engine itself.

But you should be just fine with thinking that they are semantically similar and being optimized in a similar way.

The optimization wouldn't depend on the field type

Answer 3

at least in sqlserver both gives same execution plan !!!