SQL “<>”与“不在”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/858344/
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
"<>" vs "NOT IN"
提问by DJ.
I was debugging a stored procedure the other day and found some logic something like this:
前几天我正在调试一个存储过程，发现了一些类似这样的逻辑：
SELECT something
FROM someTable
WHERE idcode <> (SELECT ids FROM tmpIdTable)
This returned nothing. I thought it looked a little odd with the "<>" so I changed it to "NOT IN" and then everything worked fine. I was wondering why this is? This is a pretty old proc and I am not really sure how long the issue has been around, but we recently switched from SQL Server 2005 to SQL Server 2008 when this was discovered. What is the real difference between "<>" and "NOT IN" and has the behavior changed between Server2005 and 2008?
这没有任何回报。我认为“<>”看起来有点奇怪，所以我将其更改为“NOT IN”，然后一切正常。我想知道这是为什么？这是一个相当古老的过程，我不确定这个问题已经存在了多久，但我们最近在发现此问题时从 SQL Server 2005 切换到 SQL Server 2008。“<>”和“NOT IN”之间的真正区别是什么？Server2005 和 2008 之间的行为是否发生了变化？
回答by Tomalak
SELECT something
FROM someTable
WHERE idcode NOT IN (SELECT ids FROM tmpIdTable)
checks against any value in the list.
检查列表中的任何值。
However, the NOT IN is not NULL-tolerant. If the sub-query returned a set of values that contained NULL, no records would be returned at all. (This is because internally the NOT IN is optimized to idcode <> 'foo' AND idcode <> 'bar' AND idcode <> NULLetc., which will always fail because any comparison to NULL yields UNKNOWN, preventing the whole expression from ever becoming TRUE.)
但是，NOT IN 不是 NULL 容忍的。如果子查询返回一组包含 NULL 的值，则根本不会返回任何记录。（这是因为在内部 NOT IN 被优化为idcode <> 'foo' AND idcode <> 'bar' AND idcode <> NULL等，这将始终失败，因为与 NULL 的任何比较都会产生 UNKNOWN，从而阻止整个表达式变为 TRUE。）
A nicer, NULL-tolerant variant would be this:
一个更好的，NULL 容忍的变体是这样的：
SELECT something
FROM someTable
WHERE NOT EXISTS (SELECT ids FROM tmpIdTable WHERE ids = someTable.idcode)
EDIT: I initially assumed that this:
编辑：我最初认为：
SELECT something
FROM someTable
WHERE idcode <> (SELECT ids FROM tmpIdTable)
would check against the first value only. It turns out that this assumption is wrong at least for SQL Server, where it actually triggers his error:
只会检查第一个值。事实证明，这个假设至少对于 SQL Server 来说是错误的，它实际上触发了他的错误：
Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
回答by KM.
try this, may run faster because of index usage:
试试这个，可能因为索引使用而运行得更快：
SELECT something
FROM someTable
LEFT OUTER JOIN tmpIdTable ON idcode=ids
WHERE ids IS NULL
回答by Rob
<>is a "singular" NOToperation; NOT INis a set operation, so it makes sense that the former wouldn't work. I have no idea whether or not it may have done so under a previous version of SQL Server, however.
<>是“单一”NOT操作；NOT IN是一个集合操作，所以前者不起作用是有道理的。但是，我不知道在以前版本的 SQL Server 下是否可以这样做。
回答by Cade Roux
This code is valid if and only if there are no rows or a single row returned from tmpIdTable:
当且仅当没有从 tmpIdTable 返回的行或单行时，此代码才有效：
SELECT something
FROM someTable
WHERE idcode <> (SELECT ids FROM tmpIdTable)
If multiple rows are returned, you will get an error like:
如果返回多行，您将收到如下错误：
Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
消息 512，级别 16，状态 1，第 1 行子查询返回了 1 个以上的值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的。
This is the same error you get with nested scalar unexpectedly produces multiple rows like:
这与嵌套标量意外产生多行的错误相同，例如：
SELECT *, (SELECT blah FROM t1 WHERE etc.) FROM t2
SELECT *, (SELECT blah FROM t1 WHERE etc.) FROM t2
Nothing has changed WRT this in SQL Server in a decade, so I expect assumptions about the nested query in the original code have been broken.
十年来，SQL Server 中的 WRT 没有发生任何改变，因此我预计原始代码中有关嵌套查询的假设已被打破。
If no rows are returned, the result will be empty since <> NULLis never true (assume ANSI NULLs).
如果没有返回任何行，则结果将为空，因为<> NULL它永远不会为真（假设 ANSI NULL）。
This code is valid for any number of rows:
此代码适用于任意数量的行：
SELECT something
FROM someTable
WHERE idcode NOT IN (SELECT ids FROM tmpIdTable)
However, there still can be issues with NULL.
但是，NULL 仍然存在问题。
回答by Walter Mitty
If the SELECT subquery returns zero rows, that's a NULL. When NULL is compared to anything, the result is always UNKNOWN, and never TRUE. Confusingly enough, NOT UNKNOWN is equal to UNKNOWN.
如果 SELECT 子查询返回零行，则为 NULL。当 NULL 与任何东西进行比较时，结果总是 UNKNOWN，从不为 TRUE。令人困惑的是，NOT UNKNOWN 等于 UNKNOWN。
I avoid three valued logic (TRUE, FALSE, UNKNOWN) whenever possible. It's not that hard to avoid once you get the hang of it.
我尽可能避免三值逻辑（TRUE、FALSE、UNKNOWN）。一旦掌握了窍门，就不会那么难避免。
If the SELECT subquery returns exactly one value, the comparison for inequality should return the result you expect.
如果 SELECT 子查询只返回一个值，则不等式比较应返回您期望的结果。
If the SELECT subquery returns more than one value, you should get an error.
如果 SELECT 子查询返回多个值，您应该得到一个错误。
In general, NOT IN will return the result you expect when you are testing for non membership in a set.
通常，当您测试集合中的非成员资格时，NOT IN 将返回您期望的结果。
This response overlaps other responses, but it's phrased a little differently.
此响应与其他响应重叠，但措辞略有不同。
Edited to add more detail about NOT IN:
编辑以添加有关 NOT IN 的更多详细信息：
I did some searching about NOT IN in Oracle, and I learned something I didn't know a half an hour ago. NOT IN is NULL sensitive. In particular,
我在 Oracle 中搜索了一些关于 NOT IN 的内容，我学到了半小时前我不知道的东西。NOT IN 对 NULL 敏感。特别是，
X NOT IN (SELECT ...)
Is not the same as
不一样
NOT (X IN SELECT ...))
I may have to amend my earlier response!
我可能需要修改我之前的回复！
回答by Peter Perhá?
I have no idea why would you write something like WHERE idcode <> (SELECT ids FROM tmpIdTable). A SELECT statement will return a set of tuples and your idcode either will or will NOT be IN this set. "WHERE idcode NOT IN (SELECT ids FROM tmpIdTable)" is the way to do it.
我不知道你为什么要写类似的东西WHERE idcode <> (SELECT ids FROM tmpIdTable)。SELECT 语句将返回一组元组，您的 idcode 将或不会在此组中。" WHERE idcode NOT IN (SELECT ids FROM tmpIdTable)"是这样做的方法。
回答by A-K
queries using NOT IN may be brittle:
使用 NOT IN 的查询可能很脆弱：
回答by Vuk
in some versions of SQL !=should be used for a "not equals" logical statement. Have you tried that?
在某些版本的 SQL 中!=应该用于“不等于”逻辑语句。你试过吗？
