Geek Answers Handbook

SQL difference between IN and OR in WHERE

What is the difference between the following two teams?

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 a different type?

+7
source share
5 answers

They are semantically identical.

IN is simply a shorthand for equality operators, as in your second example. Performance should also be identical.

The type should not matter; it will always evaluate the string of equalities.

There is a difference when you use NOT IN and data that may be NULL , although - NULL will not evaluate to false to compare NOT IN , so you can get strings that you did not expect in the result set.

As an example:

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

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

+10
source

It depends on the concrete implementation of the DBMS optimizer and the mechanism itself.

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

The optimization will not depend on the type of field

+1
source

at least in sqlserver both give the same execution plan !!!

+1
source

Each DBMS is robust enough to preform the IN statement much better because of the data structure. Moreover, when db calculates the sql plan, it does not necessarily convert the OR form to the IN form, only because the OR operator can combine different conditions as a whole. From a logical point of view, they are exactly the same.

0
source

I think IN is faster because you are giving a simpler request that the server can handle. Just my thought.

See the following comment by Fernando Giovanini in this article, he mentions that IN makes it not only more readable, but also faster: http://www.ajaxline.com/32-tips-to-speed-up-your-mysql -queries # comment-325677194

-3
source

More articles:


All Articles