Diffreence b / w“IN” &安培; " OR"在SQL中 [英] Diffreence b/w "IN" & "OR" in SQL

问题描述

以下两个命令有什么区别？

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

两者都一样吗？如果YE那么，哪一个更快？

both are same? if YEs then, Which one is faster?

推荐答案

一般来说，我同意这个答案：http://stackoverflow.com/questions/2110715/sql-server-select-in-vs-or [ ^ ]。我只想说使用 IN 更容易编写和阅读 - 至少对我而言;）

Generally, i agree with this answer: http://stackoverflow.com/questions/2110715/sql-server-select-in-vs-or[^]. I would only to say that using IN is easier to write and read - at least for me ;)

引用：

它们在语义上是相同的。



IN只是一串等式语句的简写在你的第二个例子中。性能也应该相同。



类型无关紧要，它总是会评估为一系列相等的。



虽然使用NOT IN和数据可能为NULL但存在差异 - 对于NOT IN比较，NULL不会评估为false，因此您可能会得到结果集中没有预期的行。



例如：



SELECT'Passsed！'WHERE NULL NOT IN（'foo' ，'bar'）



上面的查询不会返回一行，即使面值为NULL也不是'foo'或'bar' - 这是因为NULL是一个未知状态，SQL不能肯定地说未知值不是IN列出的值之一。

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.

在Sql Server中，它们都是一样的。

你也可以查看他们的执行计划。

In Sql Server, they are both same.
You can have a look at their execution plans as well.

这篇关于Diffreence b / w“IN” &安培; " OR"在SQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！