结构化查询语言/SELECT：IS NULL 谓词

创建新行时，我们可能不知道一列或多列的值。

假设我们要存储有关银行账户的信息，并且其中一个账户的余额我们不知道。我们该怎么办？有几种可能性

• 拒绝包含所有其他信息的整行，例如账户号、可用信用额度、利率等。这不是很吸引人。
• 存储默认值而不是我们当前不知道的值。但是，在某些情况下，不可能定义默认值，因为每个值都是可能的，例如，银行账户为“0”或“-1”并不罕见。
• 存储一个标志，指示没有存储值。这种方法类似于非数字技术。

关系型数据库管理系统使用最后提到的技术，标志的意思是“没有存储值”。有时人们说“存储了NULL值”或“存储了NULL特殊标记”。

布尔逻辑的扩展

假设有一个用于银行账户的表格，其中一些行在余额列中包含 NULL 特殊标记。这些行是否满足“balance >= 0”或“balance <= 0”这两个 WHERE 条件中的至少一个？不。无法确定这些条件是真还是假！老实说，我们必须承认，我们不知道我们通常的真/假逻辑中的答案，因为我们不知道余额的值。我们被迫用第三个值扩展布尔值的范围，我们称之为未知。以上两个条件既不为真也不为假；对于余额包含 NULL 特殊标记的行，它们都将评估为“未知”。

在后面的阶段，我们需要对布尔运算符 NOT、AND、OR 和 EQUAL 进行定义，以便真/假与未知进行交互。您可以在此处找到定义。

检索 NULL 特殊标记

在每个 SELECT 命令中，这样的行将成为结果行的一部分，其中 WHERE 条件评估为真。如果评估为假或未知，则该行将被拒绝。由于上面所有类似“balance >= 0”的 WHERE 条件（以及它们的否定）对于缺少余额值的行都将评估为未知，因此初步没有办法检索它们。

为了克服这种不足，SQL 包含特定的短语“IS NULL”。短语“balance IS NULL”对余额中缺少值的那些行精确评估为真。

SELECT ...
FROM   ...
WHERE  <columnname> IS NULL
...    
;

我们必须使用完全相同的措辞。使用任何算术运算符，如 >、<=、!= 等，都不会检索包含 NULL 特殊标记的行。即使对于条件“（balance = 0）OR NOT（balance = 0）”，它也是传统真/假逻辑中的重言式，也是如此。除了这个 IS NULL 谓词之外，没有其他方法可以检索 NULL 特殊标记，除非有一个简单但无用的例外：如果省略 WHERE 条件，则将检索表格的所有行，包括任何列中包含和不包含 NULL 特殊标记的行。

就这样！如果您第一次遇到此主题，处理 NULL 特殊标记和 3 值逻辑听起来可能很奇怪。但是由于 IS NULL 谓词始终评估为真或假，因此之后一切照常进行。我们可以使用 SELECT 命令的其他所有元素（布尔逻辑、连接、having、order by 等），就像我们迄今为止所做的那样。

一些示例

我们的测试数据库不包含 NULL 特殊标记。然而，我们在解释外连接期间遇到了这种情况。外连接会创建结果行，其中一些列包含 NULL 特殊标记。如果我们处理此类子查询的结果，我们必须考虑这种可能性。

还有两种方法可以生成 NULL 特殊标记。

• 使用 NULL 特殊标记的显式表示法插入或更新命令。在这种情况下，SQL 关键字null用作 NULL 特殊标记的代表。
• 不使用所有列的 INSERT 命令。省略的列将获得 NULL 特殊标记，或者获得默认值（如果已定义）。

为了演示这一点并为以下练习创建一些示例，我们将一行放入person表格中，并留出一些列为空。

-- Insert a new row for testing purpose
INSERT INTO person (id, firstname, lastname)  VALUES (51, 'Half man', 'Uncomplete');
COMMIT;

-- Retrieve the row. As defined in CREATE TABLE statement the weight has a default value of integer 0.
-- Date_of_birth and place_of_birth contain the NULL special marker.
SELECT * FROM person WHERE  id = 51;

-- use the IS NULL predicate within WHERE clause. The result contains 1 row.
SELECT * FROM person WHERE ssn IS NULL;

-- weight has a value!! We expect to retrieve no rows when we use the IS NULL predicate.
SELECT * FROM person WHERE weight IS NULL;
-- or, to say it the other way round, the number of rows is 0
SELECT count(*) FROM person WHERE weight IS NULL;
-- but in the next statement the number of rows is 1
SELECT count(*) FROM person WHERE weight = 0;

-- Negate the IS NULL predicate
SELECT count(*) FROM person WHERE ssn IS NULL;     -- IS NULL
SELECT count(*) FROM person WHERE ssn IS NOT NULL; -- Negation of IS NULL

SELECT count(*)
FROM   person
WHERE  ssn IS NULL
OR     ssn IS NOT NULL; -- A tautology, which always retrieves ALL rows of a table
-- Same as above
SELECT count(*)
FROM   person
WHERE  ssn IS NULL
OR NOT ssn IS NULL; -- A tautology, which always retrieves ALL rows of a table

接下来，我们将展示 UPDATE 命令与关键字 NULL 结合使用的示例

--
-- Insert a new row for testing purpose with all columns filled with a useful value
INSERT INTO person (id, firstname, lastname,  date_of_birth,    place_of_birth, ssn,           weight)
VALUES             (52, 'Lyn',     'Mutable', DATE'1951-05-13', 'Anchorage',    '078-05-1152', 69);
COMMIT;
SELECT * FROM person WHERE id = 52;

-- Delete a single column value (not the complete row)
UPDATE person SET ssn = null WHERE id = 52;
COMMIT;
SELECT * FROM person WHERE id = 52;      -- one row
SELECT * FROM person WHERE ssn IS NULL;  -- two rows: 51 + 52

恢复示例数据库的原始状态。

DELETE FROM person WHERE id > 50;
COMMIT;

Coalesce() 和类似函数

在 NULL 特殊标记的上下文中，我们经常需要检索没有值（NULL 特殊标记）或默认值（如 0 或空白）的行。在这种情况下，WHERE 条件类似于“... WHERE (col IS NULL OR col = 0) ...”。为了使源代码更简单，SQL 标准定义了一个函数coalesce(<expression_1>, <expression_2>)。如果第一个参数（通常是列的名称）不是 NULL，则该函数评估为该参数，否则评估为第二个参数。

示例

-- Retrieve rows without ssn or with ssn equal to blank.
SELECT *
FROM   person
WHERE  coalesce(ssn, ' ') = ' ';
-- equivalent:
-- WHERE (ssn IS NULL
-- OR     ssn = ' ');

函数名coalesce来自这样一个事实，即该函数可以接受任意数量的参数并递归地评估它们。如果参数n导致真实值，则它评估为该参数，否则该函数将调用自身而不包含第 n 个参数。coalesce(expression_1, expression_2, expression_3) 评估为 expression_1，如果 expression_1 不是 NULL，否则评估为 expression_2，如果 expression_2 不是 NULL，否则评估为 expression_3。

SQL 标准定义了另一个函数nullif(<expression_1>, <expression_2>)。如果两个表达式相等，则它评估为 NULL，如果它们彼此不同，则它评估为第一个表达式。

不同的供应商提供了一些其他函数，如isnull()、ifnull() 或nvl() 来支持处理 NULL 值。这些函数的含义是供应商特定的。

练习

插入一个新的爱好“雪鞋行走”，没有备注。

点击查看解决方案

INSERT INTO hobby (id, hobbyname,     remark)
VALUES            (10, 'Snowshoeing', null);
COMMIT;

找到上述问题的第二个解决方案，无需使用关键字“null”。（首先删除第 10 行。）

点击查看解决方案

DELETE FROM hobby WHERE id = 10;
INSERT INTO hobby (id, hobbyname)
VALUES            (10, 'Snowshoeing');
COMMIT;

检索所有没有备注的爱好。

点击查看解决方案

-- 1 row
SELECT * FROM hobby WHERE remark IS NULL;

有多少爱好以备注的方式举例说明？

点击查看解决方案

-- 9 rows
SELECT count(*) FROM hobby WHERE remark IS NOT NULL;

更改第 10 行爱好，使爱好名称包含字符串“NULL”，备注包含“爱好名称未知”。

点击查看解决方案

-- Consider the two apostrophes surrounding the string 'NULL', which consists of the 4 characters N, U, L and L !!
UPDATE hobby SET hobbyname = 'NULL', remark = 'Name of hobby not known' WHERE id = 10;
COMMIT;

a) 检索爱好名称为“NULL”的行。
b) 检索备注为“爱好名称未知”的行。

点击查看解决方案

-- This may be a pitfall question. There is no relation to the IS NULL predicate
SELECT * FROM hobby WHERE hobbyname = 'NULL';
SELECT * FROM hobby WHERE remark = 'Name of hobby not known';

有多少爱好有爱好名称？

点击查看解决方案

-- All 10 rows contains a hobby name, even the row with the hobbyname 'NULL'
SELECT count(*) FROM hobby WHERE hobbyname IS NOT NULL;