"Is Not Null"和"Is not Null"之间有什么区别?和“不是不为空"；

问题描述

SELECT id FROM customers WHERE type IS NOT Null;

对:

SELECT id FROM customers WHERE NOT type IS NULL;

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

有什么区别?为什么其中之一更可取?

修改:
在我看来，性能可能有所不同.有人愿意详细说明吗?

解决方案

没有区别.

所有主要引擎(即MySQL，SQL Server，Oracle和PostgreSQL)将在解析阶段合并这些谓词，并根据它们制定相同的计划.

处理这些条件比仅以一个或另一个顺序应用运算符要复杂得多.

例如，在Oracle中，IS NOT NULL(或NOT IS NULL)条件意味着可以使用索引，因此可以进行如下查询:

SELECT  column
FROM    mytable
WHERE   column IS NOT NULL

很有可能将用index fast full scan执行，并且在运行时不会进行任何其他检查(因为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

评估为假.

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?

解决方案

There is no difference.

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

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:

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).

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).

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

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)

Update:

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

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

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

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

evaluate to false.

这篇关于"Is Not Null"和"Is not Null"之间有什么区别?和“不是不为空"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！