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?
哪一个更快?如果id是另一种类型,它会有所不同吗?
5 个解决方案
#1
11
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.
IN只是第二个示例中的一系列相等语句的简写。表现也应该是相同的。
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.
但是,当您使用NOT IN和数据可能为NULL时有所不同 - 对于NOT IN比较,NULL不会评估false,因此您可能会得到结果集中没有预期的行。
As an example:
举个例子:
SELECT 'Passed!' WHERE NULL NOT IN ('foo', 'bar')
选择'通过!' 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.
上面的查询不会返回一行,即使面值为NULL也不是'foo'或'bar' - 这是因为NULL是一个未知状态,并且SQL无法肯定地说未知值不是IN列出的IN之一值。
#2
1
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
优化不依赖于字段类型
#3
1
at least in sqlserver both gives same execution plan !!!
至少在sqlserver中都给出了相同的执行计划!
#4
0
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运算符可以完全组合不同的条件。从逻辑角度来看,它们是完全相同的。
#5
-3
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 #评论-325677194
#1
11
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.
IN只是第二个示例中的一系列相等语句的简写。表现也应该是相同的。
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.
但是,当您使用NOT IN和数据可能为NULL时有所不同 - 对于NOT IN比较,NULL不会评估false,因此您可能会得到结果集中没有预期的行。
As an example:
举个例子:
SELECT 'Passed!' WHERE NULL NOT IN ('foo', 'bar')
选择'通过!' 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.
上面的查询不会返回一行,即使面值为NULL也不是'foo'或'bar' - 这是因为NULL是一个未知状态,并且SQL无法肯定地说未知值不是IN列出的IN之一值。
#2
1
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
优化不依赖于字段类型
#3
1
at least in sqlserver both gives same execution plan !!!
至少在sqlserver中都给出了相同的执行计划!
#4
0
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运算符可以完全组合不同的条件。从逻辑角度来看,它们是完全相同的。
#5
-3
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 #评论-325677194