"Is Not Null" 和 "Not Is Null" 有什么区别？

Question

SELECT id FROM customers WHERE type IS NOT Null;

比较：

SELECT id FROM customers WHERE NOT type IS NULL;

以上两者返回的数据完全相同。

它们有什么区别，并且为什么其中一个更可取？

编辑：
在性能方面可能会有所不同。 有人愿意详细说明吗？

- Icode4food

---

如果您不确定结果，请不要假设X = Y意味着在所有情况下X将等于Y。只需运行一些排列，询问大量人员，查找原始文档。在这里提问已经是一个相当聪明的举动了：） - Proclyon

在C、C#和Java中，"a != null"与"!(a == null)"是没有逻辑差异的。我敢打赌，如果有任何可测量的性能差异，它将与顺序表扫描甚至从索引读取的成本相比微不足道。 - chiccodoro

区别在于微软已经对IS NOT运算符进行了专利申请（在VB.NET的上下文中，即在SQL之后的几年）。但是很难强制执行这项专利... - Konrad Rudolph

Answer 1

没有区别。

对我来说，性能方面可能存在差异。有人可以详细解释一下吗？

所有主要的数据库引擎（包括 MySQL、SQL Server、Oracle 和 PostgreSQL）都会在解析阶段合并这些谓词，并从它们生成相同的查询计划。

处理这些条件比单纯地应用运算符的顺序更为复杂。

例如，在 Oracle 中，IS NOT NULL（或 NOT IS NULL）条件意味着可以使用索引，因此像这样的查询：

SELECT  column
FROM    mytable
WHERE   column IS NOT NULL

很可能会使用索引快速全表扫描执行，而且不会在运行时进行其他检查（因为NULL值根本进不了索引，所以没有必要检查它们）。

即使需要检查每条记录，检查顺序也将由优化器定义（而不是由谓词和运算符在WHERE子句中出现的顺序定义）。

例如，这里是一个Oracle查询的计划：

SQL> EXPLAIN PLAN FOR
  2  
  2  SELECT *
  3  FROM   t_test
  4  WHERE  NOT column IS NULL
  5  /

Explained

SQL> SELECT  *
  2  FROM    TABLE(DBMS_XPLAN.display())
  3  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 958699830
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    30 |  1260 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_TEST |    30 |  1260 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COLUMN" IS NOT NULL)

正如你所看到的，filter在内部被转换为一个IS NOT NULL（Oracle与大多数评论者认为这是一种更合适的形式）。

更新：

正如Jonathan Leffler所指出的，当评估元组（而不是单个列）时存在差异。

一个由混合的NULL和非NULL值组成的元组既不是NULL也不是NOT NULL。

在PostgreSQL中（支持对元组使用此谓词），这两个表达式都是：

SELECT  (1, NULL) IS NULL
SELECT  (1, NULL) IS NOT NULL

计算结果为假。

- Quassnoi

---

我同意“IS NOT NULL”更易读。加上括号也更安全，因为这样不会让你在本来想写“WHERE (type IS NOT NULL AND state = 1) or type = 2”时，错误地写成“WHERE NOT (type IS NULL AND state = 1) or type = 2”。 - Ike Walker

Answer 2

IS NOT NULL是一个比较运算符，就像IS NULL、=、>、<等。

NOT是一个逻辑运算符，作用于条件的其余部分。因此，您可以说NOT type = 5、NOT type IS NULL，甚至是NOT type IS NOT NULL。

我的观点是要指出，它们是两个非常不同的运算符，尽管结果相同。当然，在布尔逻辑中，NOT (column IS NULL)和column IS NOT NULL之间没有区别，但了解差异是明智的。

至于性能，IS NOT NULL可能会比NOT ... IS NULL节省一些周期，因为您使用了单个运算符而不是两个运算符，但任何合理的优化器都会在查询运行之前弄清楚它们是相同的。

- wuputah

Answer 3

在通常情况下，如果LHS项是一个简单的变量或表达式，则NOT x IS NULL和x IS NOT NULL没有区别。优化器会将这两个相同地处理。

然而，在完整的SQL中，LHS项不限于简单的变量或表达式；在正式语法中，LHS是一个<row value predicand>：

SQL / Foundation-ISO/IEC 9075-2：2003

§8.7 <null predicate> (p395)

Specify a test for a null value.

<null predicate> ::= <row value predicand> <null predicate part 2>

<null predicate part 2> ::= IS [ NOT ] NULL

追踪语法，您会发现：

§7.2 <row value expression> (p296)

Specify a row value.

[...]

<row value predicand>  ::=
       <row value special case>
 |     <row value constructor predicand>

<row value special case> ::= <nonparenthesized value expression primary> 

同时：

§7.1 <row value constructor> (p293)

Specify a value or list of values to be constructed into a row or partial row.

<row value constructor> ::=
       <common value expression>
 |     <boolean value expression>
 |     <explicit row value constructor>

[...]

<row value constructor predicand> ::=
       <common value expression>
 |     <boolean predicand>
 |     <explicit row value constructor>

继续进行。 （通过SQL标准追踪任何内容都是很困难的工作。您可以在http://savage.net.au/SQL/上找到一个大量超链接的标准版本。）

然而，正如您从“行值”的提及中所猜测的那样，您可以将多个简单表达式组合在LHS上形成“行值构造器谓词”。然后这两种形式之间存在差异。

从概念上讲，您有：

(val1, val2, val3) IS NOT NULL

vs

NOT (val1, val2, val3) IS NULL

现在，在第一种情况下，如果val1、val2和val3都不为NULL，则返回TRUE。在第二种情况下，如果val1、val2或val3中任意一个不为NULL，则返回TRUE。因此，存在两种操作不相同的情况。

然而，正如我之前所说，对于简单列或表达式的通常情况，这两种操作没有区别。

- Jonathan Leffler

---

好观点，+1。实际上，在PostgreSQL中它就是这样工作的。 - Quassnoi

这是规范中的一般结构，它也适用于 RHS 侧的其他运算符。例如，SELECT NOT true = ANY(ARRAY[false, false, true]);与 SELECT true <> ANY(ARRAY[false, false, true]);。 - Evan Carroll