“Is Not Null”和“Not Is Null”之间的区别是什么？

SELECT id FROM customers WHERE type IS NOT Null;

Versus:

与：

SELECT id FROM customers WHERE NOT type IS NULL;

The data that either of the above will return will be exactly the same.

上述任何一个都将返回的数据将完全相同。

What is the difference and why would one of them be preferable?

有什么区别，为什么其中一个更可取？

Edit:
It seems to me that there might be a difference when it comes to performance. Anyone care to elaborate on this?

编辑：在我看来，在性能方面可能会有所不同。有人关心这个吗？

3 个解决方案

#1

There is no difference.

没有区别。

It seems to me that there might be a difference when it comes to performance. Anyone care to elaborate on this?

在我看来，在性能方面可能会有所不同。有人关心这个吗？

All major engines (that is MySQL, SQL Server, Oracle and PostgreSQL) will merge these predicates on parsing stage, making identical plans from them.

所有主要引擎（即MySQL，SQL Server，Oracle和PostgreSQL）都将在解析阶段合并这些谓词，从而制定相同的计划。

Handling of these conditions is more complex that mere applying operators in one or another order.

处理这些条件比仅仅按一个或另一个顺序应用操作员更复杂。

For instance, in Oracle, an IS NOT NULL (or NOT IS NULL) condition implies a possibility to use an index, so a query like this:

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

SELECT  column
FROM    mytable
WHERE   column IS NOT NULL

will most probably be executed with an index fast full scan, with no additional checks made in runtime (since the NULL values just won't make it into the index, so it's no use to check them).

很可能会使用索引快速全扫描执行，而不会在运行时进行额外的检查（因为NULL值不会使其进入索引，因此检查它们没有用）。

Even if each record would need to be checked, the order of checks will be defined by the optimizer (and not by the order the predicates and operators appear in the WHERE clause).

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

For instance, here is a plan for an Oracle query:

例如，这是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)

As you can see, the filter was translated internally into an IS NOT NULL (which Oracle along with most commenters seems to believe to be a more appropriate form)

正如您所看到的，过滤器在内部被转换为IS NOT NULL（Oracle和大多数评论者似乎认为它是更合适的形式）

Update:

更新：

As Jonathan Leffler pointed out, these is difference when evaluating tuples (as opposed to single columns).

正如Jonathan Leffler所指出的那样，这些在评估元组时是不同的（而不是单列）。

A tuple consisting of mixed NULL and non-NULL values is neither a NULL nor a NOT NULL.

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

In PostgreSQL (which supports this predicate against tuples), both these expressions:

在PostgreSQL（支持这个针对元组的谓词）中，这两个表达式：

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

evaluate to false.

评价为假。

#2

IS NOT NULL is a comparison operator, just like IS NULL or =, >, <, etc.

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

NOT is a logical operator that acts on the rest of the condition. So you can say NOT type = 5, NOT type IS NULL, or even NOT type IS NOT NULL.

NOT是一个逻辑运算符，它对其余条件起作用。所以你可以说NOT type = 5，NOT type IS NULL，甚至NOT not type IS NOT NULL。

My point here is to point out that they are two very different operators, even though the result is the same. Of course, in boolean logic, there is no difference between NOT (column IS NULL) and column IS NOT NULL, but it's wise to know the difference.

我的观点是要指出它们是两个非常不同的运算符，即使结果是相同的。当然，在布尔逻辑中，NOT（列IS NULL）和列IS NOT NULL之间没有区别，但知道差异是明智的。

As for performance, IS NOT NULL might save you a few cycles over NOT ... IS NULL because you are using a single operator instead of two operators, but any reasonable optimizer will figure out they are the same thing before the query is run.

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

#3

In the usual case where the LHS term is a simple variable or expression, there is no difference between NOT x IS NULL and x IS NOT NULL. The optimizer will treat the two identically.

在LHS术语是简单变量或表达式的通常情况下，NOT x IS NULL和x IS NOT NULL之间没有区别。优化器将对两者进行相同的处理。

However, in full SQL, the LHS term is not limited to being a simple variable or expression; in the formal grammar, the LHS is a <row value predicand>:

但是，在完整的SQL中，LHS术语不仅限于简单的变量或表达式;在形式语法中，LHS是 <行值谓词> ：

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

§8.7 <null predicate> (p395)

§8.7 （p395） 谓词>

Specify a test for a null value.

指定空值的测试。

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

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

And chasing through the grammar, you find that:

通过语法追逐，您会发现：

§7.2 <row value expression> (p296)

§7.2 <行值表达式> （p296）

Specify a row value.

指定行值。

[...]

[...]

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

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

And:

和：

§7.1 <row value constructor> (p293)

§7.1 <行值构造函数> （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>

And so it goes on. (Chasing anything through the SQL standard is hard work. You can find a heavily hyperlinked version of the standard at http://savage.net.au/SQL/.)

所以它继续。 （通过SQL标准追逐任何东西都是很难的。你可以在http://savage.net.au/SQL/找到标准的高度超链接版本。）

However, as you may guess from the mention of 'row value', you can have multiple simple expressions combined on the LHS to form a 'row value constructor predicand'. And then there is a difference between the two forms.

但是，正如您在提到“行值”时可能猜到的那样，您可以在LHS上组合多个简单表达式以形成“行值构造函数谓词”。然后两种形式之间存在差异。

Conceptually, you have:

从概念上讲，你有：

(val1, val2, val3) IS NOT NULL

vs

VS

NOT (val1, val2, val3) IS NULL

Now, in the first case, you get TRUE if each of val1, val2 and val3 is not NULL. In the second case, you get TRUE if any one of val1, val2, val3 is not NULL. So, there are circumstances where the two operations are not identical.

现在，在第一种情况下，如果val1，val2和val3中的每一个都不为NULL，则为TRUE。在第二种情况下，如果val1，val2，val3中的任何一个不为NULL，则为TRUE。因此，在某些情况下，这两种操作并不相同。

However, as I said up front, for the usual case of a simple column or expression, there is no difference between the two.

但是，正如我前面所说，对于简单列或表达式的通常情况，两者之间没有区别。