WHERE 中 IN 和 OR 之间的 SQL 区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9024594/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL difference between IN and OR in WHERE
提问by DrXCheng
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 idis another type?
哪个更快？如果id是另一种类型会有所不同吗？
回答by JNK
They are semantically identical.
它们在语义上是相同的。
INis just a shorthand for a string of equality statements like in your second example. Performance should also be identical.
IN只是像第二个示例中那样的一串相等语句的简写。性能也应该相同。
The type shouldn't matter, it will always evaluate to a string of equalities.
类型无关紧要，它总是会评估为一串等式。
There is a difference when you are using NOT INand data that can be NULL, though - a NULLwill not evaluate false for a NOT INcomparison, so you may get rows you didn't expect in the result set.
但是，当您使用NOT IN和 data 时可能会NULL有所不同 - aNULL不会为NOT IN比较评估 false ，因此您可能会在结果集中得到您不期望的行。
As an example:
举个例子：
SELECT 'Passed!' WHERE NULL NOT IN ('foo', 'bar')
SELECT 'Passed!' WHERE NULL NOT IN ('foo', 'bar')
The above query will not return a row even though at face value NULLis neither 'foo'or 'bar'- this is because NULLis an unknown state, and SQL cannot say with certainty that the unknown value is NOTone of the INlisted values.
上面的查询将不会返回一行，即使在面值NULL既不'foo'或者'bar'-这是因为NULL是一个未知的状态，和SQL不能肯定地说，未知值是不是对的一个IN列出的值。
回答by zerkms
This depends on specific DBMS optimizer implementation and engine itself.
这取决于特定的 DBMS 优化器实现和引擎本身。
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
优化不依赖于字段类型
回答by Luis Siquot
at least in sqlserver both gives same execution plan !!!
至少在 sqlserver 中，两者都给出了相同的执行计划！！！
回答by Dimitri
Every DBMS reliable enough preforms IN operator much better because of the data structure. Moreover, when the db calculates a sql plan, it does not necessarily translates the OR form into the IN form, just because OR operator could combine different conditions altogether. From logical perspective they are quite the same.
由于数据结构的原因，每个 DBMS 都足够可靠，因此可以更好地执行 IN 运算符。而且，db在计算sql计划的时候，并不一定要把OR形式转成IN形式，只是因为OR运算符可以将不同的条件组合在一起。从逻辑的角度来看，它们是完全相同的。
回答by Rick Kuipers
I think IN is faster purely because you give an easier query that the server can handle. Just my thought.
我认为 IN 更快纯粹是因为您提供了服务器可以处理的更简单的查询。只是我的想法。
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
请参阅 Fernando Giovanini 在本文中的以下评论，他提到 IN 不仅使其更具可读性，而且速度更快：http: //www.ajaxline.com/32-tips-to-speed-up-your-mysql-queries #comment-325677194
