结构化查询语言/空值和三值逻辑

问题

如本维基教科书的前一章和维基百科中所述，有时一行中的一列没有值，或者换句话说，该列存储的是空值标记（一个表示没有数据的标记），或者使用 SQL 标准的概念，该列存储空值。此空值标记与数值零或长度为零个字符的字符串完全不同！通常，当应用程序尚未在该行的列中存储任何内容时，就会出现这种情况。

（对 Oracle 用户的提示：对于 Oracle，空值标记与长度为零个字符的字符串相同。）

空值标记的存在引入了一个新的基本问题。在通常的布尔逻辑中，存在两个逻辑值 TRUE 和 FALSE。每个比较都评估为这两个值中的一个，并且比较的否定评估为相反的值。如果一个比较评估为 TRUE，则其否定评估为 FALSE，反之亦然。例如，在通常的布尔逻辑中，以下两个比较之一为 TRUE，另一个为 FALSE：'x < 5'，'x >= 5'。

现在想象 x 现在持有空值标记的新情况。说 'NULL < 5' 为真 (1) 是不可行的。但我们可以说 'NULL < 5' 为假 (2) 并且其否定 'NULL >= 5' 为真 (3) 吗？(3) 比 (1) 更可行吗？当然不是。(1) 和 (3) 具有相同的“真实程度”，因此它们应该评估为相同的值。并且此值必须与 TRUE 和 FALSE 不同。

因此，通常的布尔逻辑通过第三个逻辑值扩展。它被命名为UNKNOWN。根据定义，所有与空值标记的比较都导致此新值。并且众所周知的语句“如果一个语句为真，则其否定为假”会丢失，因为存在第三种选择。

SQL 的逻辑是这种所谓的三值、三元或三值逻辑 (3VL)的实现。SQL 中空值标记的存在并非没有争议。但如果接受空值，则 3VL 是必要的。

此页面分两个阶段进行：首先，它解释了与比较、分组等相关的空值的处理方式。其次，它解释了新值 UNKNOWN 与任何其他布尔值（包括自身）交互时的布尔逻辑。

示例表

为了演示空值行为，我们定义了示例表：t1 和 t2。

CREATE TABLE t1 (
  id    DECIMAL PRIMARY KEY,
  col_1 DECIMAL,
  col_2 VARCHAR(20),
  col_3 DECIMAL
);

INSERT INTO t1 VALUES ( 1,  1,    'Hello World', 1);
INSERT INTO t1 VALUES ( 2,  2,    NULL,          2);
INSERT INTO t1 VALUES ( 3,  3,    'Hello World', NULL);
INSERT INTO t1 VALUES ( 4,  4,    'Hello World', NULL);
INSERT INTO t1 VALUES ( 5,  5,    'Hello Their', NULL);
INSERT INTO t1 VALUES ( 6,  NULL, 'Hello World', NULL);
INSERT INTO t1 VALUES ( 7,  NULL, 'Hello World', NULL);
INSERT INTO t1 VALUES ( 8,  8,    'Hello World', NULL);
INSERT INTO t1 VALUES ( 18, 18,   'Hello World', NULL);

CREATE TABLE t2 (
  id DECIMAL PRIMARY KEY,
  col_x DECIMAL
);

INSERT INTO t2 VALUES ( 1, 1 );
INSERT INTO t2 VALUES ( 2, NULL );
INSERT INTO t2 VALUES ( 3, 3 );
INSERT INTO t2 VALUES ( 4, 4 );
INSERT INTO t2 VALUES ( 5, 5 );
INSERT INTO t2 VALUES ( 18, 18 );
COMMIT;

步骤 1：空值的评估

比较谓词，IS NULL 谓词

SQL 认识六个比较谓词 <，<=，=，>=，> 和 <>（不等于）。它们的主要目的是对数值进行算术比较。它们中的每一个都需要两个变量或常量 (中缀表示法)。这意味着一个或两个操作数都可能持有空值标记。如前所述，常见且非常简单的规则是：“所有与空值标记的比较根据定义都会导致此新值 (unknown)。”以下是一些示例

NULL = 5 评估为 UNKNOWN。
5 = NULL 评估为 UNKNOWN。
NULL <= 5 评估为 UNKNOWN。
col_1 = 5 评估为 UNKNOWN（对于 col_1 持有空值标记的行）。
col_1 = col_2 评估为 UNKNOWN（对于 col_1 或 col_2 持有空值标记的行）。
NULL = NULL 评估为 UNKNOWN。
col_1 = col_2 评估为 UNKNOWN（对于 col_1 和 col_2 持有空值标记的行）。

WHERE 子句返回那些评估为 TRUE 的行。它不返回那些评估为 FALSE 或 UNKNOWN 的行。因此，不能保证以下 SELECT 会返回完整的表 t1

-- This SELECT will not return such rows where col_1 holds the NULL marker.
SELECT *
FROM   t1
WHERE  col_1 > 5
OR     col_1 = 5
OR     col_1 < 5;

当然，存在必须检索包含空值标记的行的用例。由于算术比较无法做到这一点，因此必须使用另一种语言结构。这就是IS NULL 谓词。

-- This SELECT will return exactly these rows where col_1 holds the NULL marker.
SELECT *
FROM   t1
WHERE  col_1 IS NULL;

其他谓词

对于其他谓词，没有简单的经验法则。必须逐个解释它们。

IN 谓词是 OR 运算序列的快捷方式

只有两个比较 'col_1 = 3' 和 'col_1 = 18' 才能检索行（可能有多行）。比较 'col_1 = NULL' 永远不会评估为 TRUE。它始终为 UNKNOWN，即使 col_1 持有空值标记也是如此。要检索这些行，有必要（如上所示）使用 'IS NULL' 谓词。

-- Shortcut for: col_1 = 3 OR col_1 = 18 OR col_1 = NULL
SELECT *
FROM   t1
WHERE  col_1 IN (3, 18, NULL);  -- the NULL case will never hit with the IN predicate!

这稍微复杂一些。这只会返回 1、3 和 4，即 t2.col_x 或 t1.col_1 中没有 NULL 的项。

SELECT *
FROM   t1
WHERE  col_1 IN (SELECT col_x FROM t2 WHERE id < 10);

EXISTS 谓词的子查询如果检索到的行的基数大于 0，则评估为 TRUE，如果基数为 0，则评估为 FALSE。不可能出现 UNKNOWN 值。

-- The subselect to t2 can hit some rows - or not. If there are hits in the subselect, ALL rows of t1
-- are returned, else no rows of t1 are returned.
SELECT *                                 -- The select to table t1
FROM   t1
WHERE  EXISTS
       (SELECT * FROM t2 WHERE id < 10); -- The subselect to table t2

LIKE 谓词将一列与正则表达式进行比较。如果该列包含空值标记，则 LIKE 谓词返回 UNKNOWN 值，这意味着该行不会被检索。

-- The LIKE retrieves only rows where col_2 matches the WHERE statement and col_2 is not NULL.
SELECT *
FROM   t1
WHERE  col_2 LIKE 'Hello %';

预定义函数

聚合函数COUNT(<column_name>), MIN(<column_name>), MAX(<column_name>), SUM(<column_name>) 和 AVG(<column_name>) 会忽略 <column_name> 包含空值标记的行。另一方面，COUNT(*) 会包含所有行。

如果标量函数（如 UPPER()、TRIM()、CONCAT()、ABS()、SQRT() 等）的参数包含空值标记，则结果值（在大多数情况下）是空值标记。

分组

在某些情况下，需要比较列值以确定它们是否不同。对于一般的数字和字符串，这种比较的结果很明显。但是，DBMS 如何处理 NULL 标记呢？它们彼此不同吗，它们彼此相等吗，还是根本没有答案？为了获得大多数用户期望的结果，标准定义了“两个 NULL 值不不同”，它们构成一个组。

SELECT DISTINCT col_1 FROM t1; 仅检索 col_1 中包含 NULL 标记的所有行中的唯一一行。

... GROUP BY col_1 ...; 为 col_1 中包含 NULL 标记的所有行创建一个组。

步骤 2：三值逻辑 (3VL) 中的布尔运算

在我们了解了如何对 NULL 标记进行各种比较和谓词运算来生成 TRUE、FALSE 和 UNKNOWN 后，有必要解释新逻辑值 UNKNOWN 的规则。

检查

第一个基本操作是检查一个真值：它是 TRUE、FALSE 还是 UNKNOWN？与 IS NULL 谓词类似，还有三个附加谓词

IS [NOT] TRUE
IS [NOT] FALSE
IS [NOT] UNKNOWN

-- Check for 'UNKNOWN'
SELECT *
FROM   t1
WHERE  (col_1 = col_3) IS UNKNOWN;  -- parenthesis are not necessary

-- ... is semantically equivalent to
SELECT *
FROM   t1
WHERE  col_1 IS NULL 
OR     col_3 IS NULL;

在逻辑系统的抽象语法中，p 代表任何真值。以下是三值逻辑真值表

p	IS TRUE	IS FALSE	IS UNKNOWN	IS NOT TRUE	IS NOT FALSE	IS NOT UNKNOWN
TRUE	TRUE	FALSE	FALSE	FALSE	TRUE	TRUE
FALSE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
UNKNOWN	FALSE	FALSE	TRUE	TRUE	TRUE	FALSE

所有谓词都导致 TRUE 或 FALSE，而不是 UNKNOWN。

NOT

下一个操作是对新值的否定。哪些值被评估为“NOT UNKNOWN”？UNKNOWN 值表示无法在 TRUE 和 FALSE 之间进行决策。不可能将这种不确定的否定导致 TRUE 或 FALSE。同样，它是 UNKNOWN。

-- Which rows will match?  (1)
SELECT *
FROM   t1
WHERE  NOT col_2 = NULL; -- 'col_2 = NULL' evaluates to UNKNOWN in all cases, see above.

-- Is this SELECT equivalent to the first one?  (2)
SELECT *
FROM   t1
  EXCEPT
SELECT *
FROM   t1
WHERE  col_2 = NULL;

-- No, it's different!! Independent from NULL markers in col_2, (1) retrieves
-- absolutely NO row and (2) retrieves ALL rows.

上面的 SELECT (1) 将不检索任何行，因为“NOT col_2 = NULL”与“col_2 = NULL”的评估结果相同，即 UNKNOWN。而 SELECT (2) 将检索所有行，因为 EXCEPT 后的部分将不检索任何行，因此仅与 EXCEPT 之前的部分相关。

在逻辑系统的抽象语法中，p 代表任何真值，而 NOT p 代表其否定。以下是适用的表格

p	NOT p
TRUE	FALSE
FALSE	TRUE
UNKNOWN	UNKNOWN

AND、OR

存在两个二元运算 AND 和 OR。它们的评估方式如下

p	q	p AND q	p OR q
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE
TRUE	UNKNOWN	UNKNOWN	TRUE
FALSE	TRUE	FALSE	TRUE
FALSE	FALSE	FALSE	FALSE
FALSE	UNKNOWN	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	TRUE
UNKNOWN	FALSE	FALSE	UNKNOWN
UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN

操作的优先级按通常的方式定义：IS 谓词、NOT、AND、OR。

一些示例

--
-- Add a new row to the test data base
INSERT INTO person (id, firstname, lastname)  -- Omit some columns to generate NULL markers
VALUES             (99, 'Tommy',   'Test');
COMMIT;

SELECT *
FROM   person
-- focus all tests to the new row
WHERE  id = 99                  -- (1): TRUE 
AND                             -- (3): (1) AND (2) ==> TRUE AND UNKNOWN ==> UNKNOWN
(
       date_of_birth = NULL     -- (2): UNKNOWN
);                              -- no hit

SELECT *
FROM   person
WHERE  id = 99                  -- (1): TRUE 
AND                             -- (3): (1) AND (2) ==> TRUE AND TRUE ==> TRUE
(
       date_of_birth IS NULL    -- (2): TRUE
);                              -- hit

SELECT *
FROM   person
WHERE  id = 99                  -- (1): TRUE 
OR                              -- (3): (1) OR (2) ==> TRUE OR UNKNOWN ==> TRUE
(
       date_of_birth = NULL     -- (2): UNKNOWN
);                              -- hit

SELECT *
FROM   person
WHERE  id = 99                  -- (1): TRUE 
AND                             -- (4): (1) AND (3) ==> TRUE AND FALSE ==> FALSE
(
NOT                             -- (3): NOT (2) ==> NOT TRUE ==> FALSE
       date_of_birth IS NULL    -- (2): TRUE

);                              -- no hit (same as AND date_of_birth IS NOT NULL)

-- Clean up the test database
DELETE FROM person WHERE id = 99;
DROP TABLE IF EXISTS t1, t2;     
COMMIT;

递归

结构化查询语言
空值和三值逻辑

事务