"<>"和"NOT IN"的区别

Question

有一天我在调试存储过程时，发现了类似于以下逻辑：

SELECT something
FROM someTable
WHERE idcode <> (SELECT ids FROM tmpIdTable)

没有返回任何结果。我觉得它使用"<>"看起来有点奇怪，所以我改成了"NOT IN"，然后一切都正常了。我想知道这是为什么？这是一个相当古老的过程，我不太确定问题存在了多长时间，但我们最近从SQL Server 2005切换到SQL Server 2008时发现了这个问题。"<>"和"NOT IN"之间的真正区别是什么？在Server2005和2008之间是否有行为变化？

- DJ.

Answer 1

SELECT something
FROM someTable
WHERE idcode NOT IN (SELECT ids FROM tmpIdTable)

检查是否存在列表中的任何值。

然而，NOT IN 不支持 NULL。如果子查询返回包含 NULL 的值集合，则不会返回任何记录。(这是因为在内部 NOT IN 优化成 idcode <> 'foo' AND idcode <> 'bar' AND idcode <> NULL等表达式，由于与NULL的任何比较都会产生UNKNOWN，导致整个表达式永远无法成立为TRUE。)

一个更好的、支持NULL的变体是：

SELECT something
FROM someTable
WHERE NOT EXISTS (SELECT ids FROM tmpIdTable WHERE ids = someTable.idcode)

---

编辑：起初我假设这是：

SELECT something
FROM someTable
WHERE idcode <> (SELECT ids FROM tmpIdTable)

原本以为只会针对第一个值进行检查，但事实证明至少对于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.

- Tomalak

---

WHERE idcode NOT IN (...) is equivalent to WHERE idcode <> ALL (...) - user330315

Answer 2

尝试这个，可能会更快，因为它使用了索引：

SELECT something
FROM someTable
    LEFT OUTER JOIN tmpIdTable ON idcode=ids
WHERE ids IS NULL

- KM.

---

SQL Server通常（我还没有看到过不是这样的情况）为连接或子查询生成相同的QEP，因此性能相同。 - pipTheGeek

Answer 3

<> 是一种“单一”的 NOT 操作符；而 NOT IN 则是一种集合操作，所以前者无法使用是有道理的。然而，我无法确定它是否在之前的 SQL Server 版本中可以使用。

- Rob

Answer 4

只有当tmpIdTable返回零行或一行时，此代码才有效。

SELECT something
FROM someTable
WHERE idcode <> (SELECT ids FROM tmpIdTable)

如果返回多行数据，你将会收到如下错误信息：

Msg 512, Level 16, State 1, Line 1 子查询返回了多个值。当子查询跟随 =、!=、<、<=、>、>= 运算符或者用作表达式时，这是不被允许的。

这与嵌套标量意外地生成多行所产生的错误相同，例如：

SELECT *, (SELECT blah FROM t1 WHERE etc.) FROM t2

在 SQL Server 中，这方面的情况已经持续了十年之久，因此我认为原始代码中关于嵌套查询的假设已经被打破。

如果没有返回任何行，则结果将为空，因为 <> NULL 永远不为真（假定 ANSI NULLs）。

该代码适用于任意数量的行：

SELECT something
FROM someTable
WHERE idcode NOT IN (SELECT ids FROM tmpIdTable)

然而，NULL 仍然可能存在问题。

- Cade Roux

---

我今天早上和一位同事有过这样的对话。虽然相似，但并不完全是答案。 - DJ.

从概念上讲，如果子查询返回零行或一行，则即使是这样也是错误的。因为您正在询问标量idcode是否等于零个或一个元素列表。即使它等于一个元素列表中的唯一元素，标量也实际上不等于列表。 - Carl Manaster

事实上，我从来不使用这个结构，通常会认为它是代码异味。 - Cade Roux

Answer 5

我不知道为什么你会写类似于WHERE idcode <> (SELECT ids FROM tmpIdTable)这样的语句。一个SELECT语句将返回一组元组，你的idcode要么在这个集合中，要么不在。"WHERE idcode NOT IN (SELECT ids FROM tmpIdTable)"是正确的写法。

- Peter Perháč

---

这就是为什么我知道如何修复它，只是我不太清楚背后的逻辑是什么。 - DJ.

如果子查询有某种排序方式，例如“在列表中<>最大的[东西]”，则<>语句可能有意义。 - Tomalak

你说得对，如果子查询有排序，<> 符号可能会有一些意义，但我仍然会避免使用它，因为它容易出错 -- 很容易忽略其意图。稍后删除或更改子查询的排序的人可能会对查询为什么返回空值感到困惑。 - Peter Perháč

为了消除歧义，在子查询中我总是会使用TOP 1或MAX()/GROUP BY或类似的语句。仅仅使用<>对一组值进行比较依赖于副作用太多，这对我的份额来说过于冒险。 - Tomalak

嗯...犯错是人之常情，我想。:-\ 我真的以为<> (SELECT ...)是有效的，但至少当子查询返回多个值时，SQL Server不接受它。这也不是一个好的实践方式。 - Tomalak

Answer 6

如果SELECT子查询返回零行，那么就是NULL。当NULL与任何东西进行比较时，结果总是未知的，而不是真正的。令人困惑的是，NOT UNKNOWN等于UNKNOWN。

我尽可能避免使用三值逻辑（TRUE，FALSE，UNKNOWN）。一旦你掌握了它，避免它并不难。

如果SELECT子查询恰好返回一个值，则不等式比较应返回您期望的结果。

如果SELECT子查询返回多个值，则应该会出现错误。

通常，在测试非成员资格时，NOT IN将返回您期望的结果。

这个回答与其他回答重叠，但措辞有些不同。

编辑以添加有关NOT IN的更多详细信息：

我在Oracle中搜索了一些关于NOT IN的内容，并学到了一些半小时前不知道的东西。NOT IN对NULL敏感。特别地，

X NOT IN (SELECT ...)

不是相同的

NOT (X IN SELECT ...))

我可能需要修改我之前的回答！

- Walter Mitty

Answer 7

使用 NOT IN 的查询可能会很脆弱：

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/10/21/defensive-database-programming-rewriting-queries-with-not-in.aspx

- A-K

Answer 8

当你说“NOT IN”时，它是“IN”的相反，其中你要寻找任何一个值是否包含或不包含（用逗号分隔）。然而，当包含空值时，会出现问题。至于寻找非空值，你可以在where语句中明确表示非空。如果你将非空值的条件与其他条件结合起来，我相信你会更成功。“NOT EXISTS”是一种通常推荐的方法，也是一种通常的良好实践。它更好地处理了空值的情况。

Select table.column 
From Table
Where table.column is not null 
AND table.column NOT Exists ('value1','value2','value3')

或者你可以将Not Exists改为子查询形式，例如

Select table.column 
From Table
Where table.column is not null
AND table.column NOT Exists (Select column.value from tmpIDtable)

关于这个主题的一个很好的解释可以在回复中找到这里。

- Shadrack

Answer 9

在某些版本的SQL中，应该使用!=来表示“不等于”的逻辑语句。 你试过了吗？

- Vuk

---

在SQL Server上，'<> '和'！='是等效的。没有任何版本坚持使用'！='。但是，'<> '是ANSI SQL标准的方式，尽管出于某种原因，我个人更倾向于更频繁地使用！=。 - Tomalak

Answer 10

<> 实际上可以理解为 "不是"

在 SQL WHERE NOT demo 上，我尝试了以下操作

失败：

WHERE NOT Country='Mexico' AND WHERE NOT country ='Sweden'

工作

WHERE NOT Country='Mexico' AND country<>'Sweden'

- Robert Ascherman

---

这并没有回答问题，而是在询问在子查询中使用<>和NOT IN的区别。NOT和NOT IN不是同一件事。此外，您不能像在第一个where子句中那样两次使用WHERE。 - Scratte

问题是关于 NOT IN，答案是关于 NOT。 - leox

我很确定<>表示不等于，而NOT IN表示不在。如果我使用WHERE status <> 'OK'，我是在说状态不是OK。 - undefined