五种主流数据库：查找空值

在数据库中，空值（NULL）是一个特殊值，表示缺失或者未知的数据。与其他编程语言（例如 Java、C++）不同，在 SQL 语句中判断一个值是否为空，不能使用等于或者不等于运算符。

本文比较五种主流数据库对于空值查找的实现和差异，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。

查找空值	MySQL	Oracle	SQL Server	PostgreSQL	SQLite
IS [NOT] NULL	✔️	✔️	✔️	✔️	✔️
ISNULL、NOTNULL	ISNULL()	❌	❌	✔️	✔️
IS [NOT] DISTINCT FROM	❌	❌	✔️	✔️	IS [NOT]
ISNULL、NOTNULL	✔️	❌	❌	❌	❌

IS [NOT] NULL

以下语句使用等于运算符查找没有上级领导（manager 字段为空）的员工：

-- 空值判断的错误示例
SELECT emp_name, manager
FROM employee
WHERE manager = NULL;

该查询没有返回任何结果。不过，员工表中确实存在这样的数据（“刘备”）。问题的原因在于，将一个数据与未知数据进行比较运算的结果未知，查询条件中的未知结果不会返回数据。

实际上，即使将两个未知数据进行比较，运算结果也是未知的。以下运算的结果均为未知：

NULL = 0
NULL != 0
NULL = ''
NULL != ''
NULL = NULL
NULL != NULL

0 和空字符串都是已知数据，和未知数据的比较结果都是未知的。同样，我们既不能认为两个未知数据相等，也不能认为它们不相等。

为了实现空值的判断，SQL 引入了两个特殊的运算符：IS NULL 和 IS NOT NULL，它们分别表示某个字段或者表达式的结果未知（空值）或者已知（非空）。因此，查找没有上级领导的员工应该使用以下判断条件：

SELECT emp_name, manager
FROM employee
WHERE manager IS NULL;

如果员工的 manager 字段为空，就会返回相应的数据。查询返回的结果如下：

emp_name|manager
--------|-------
刘备    | 

数据显示，“刘备”是公司的最高领导，因为他没有上级。

另外，IS NOT NULL 运算符可以查找数据不为空的字段和表达式。例如，以下语句查找有奖金的员工：

SELECT emp_name, bonus
FROM employee
WHERE bonus IS NOT NULL;

查询返回的结果如下：

emp_name|bonus 
--------|--------
刘备    |10000.00
关羽    |10000.00
张飞    |10000.00
诸葛亮  | 8000.00
孙尚香  | 5000.00
赵云    | 6000.00
法正    | 5000.00
庞统    | 2000.00
蒋琬    | 1500.00

🏷️五种数据库对于 IS [NOT] NULL 实现一致。

ISNULL/NOTNULL

PostgreSQL 以及 SQLite 实现了 ISNULL/NOTNULL 运算符，判断表达式是否为空。例如：

-- PostgreSQL
SELECT 1 ISNULL, 1 NOTNULL;

?column?|?column?|
--------+--------+
false   |true    |

-- SQLite
SELECT 1 ISNULL, 1 NOTNULL;

1 ISNULL|1 NOTNULL|
--------+---------+
       0|        1|

MySQL 提供了一个 ISNULL() 函数，当参数为空值时，返回 1；否则，返回 0。例如：

-- MySQL
SELECT isnull(1), isnull(null);

isnull(1)|isnull(null)|
---------+------------+
        0|           1|

IS [NOT] DISTINCT FROM

SQL Server 以及 PostgreSQL 实现了 IS [NOT] DISTINCT FROM 运算符，用于比较两个参数是否相同，支持空值比较。例如：

-- SQL Server 以及 PostgreSQL
SELECT emp_name, manager 
FROM employee
WHERE manager IS NOT DISTINCT FROM NULL;

emp_name|manager|
--------+-------+
刘备     |       |

SQLite 使用简写的 IS 运算符替代 IS NOT DISTINCT FROM，使用简写的 IS NOT 替代 IS DISTINCT FROM。例如：

-- SQLite
SELECT 1 IS 1, NULL IS 1;

1 IS 1|NULL IS 1|
------+---------+
     1|        0|

<=>

MySQL 实现了一个 <=> 运算符，用于比较两个参数是否相同，支持空值比较。例如：

-- MySQL
SELECT 1<=>2, NULL<=>NULL;

1<=>2|NULL<=>NULL|
-----+-----------+
    0|          1|