结构化查询语言(Structured Query Language)

问题描述

在 SQL 中，有些列可能存储 NULL 值（即不存在数据的标志）。这种 NULL 标志与数值型的零或长度为零的字符串截然不同！通常，它出现于某一行的列尚未被应用程序写入任何数据时。

提示： 在 Oracle 中，NULL 标志与空字符串相同。

NULL 的存在引入了一个新问题。在通常的布尔逻辑中，比较的结果只有两个：TRUE 或 FALSE。然而，当列的值为 NULL 时，无法简单地断言其比较结果为真或假。例如：

• NULL < 5 既不是 TRUE，也不是 FALSE。
• SQL 中引入了第三种逻辑值：UNKNOWN（未知），用于处理涉及 NULL 的所有比较。

SQL 的逻辑采用了三值逻辑（3VL）。如果接受 NULL 的存在，3VL 是必要的。

---

示例表

为了演示 NULL 的行为，定义以下两张表：

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;

---

第一步：评估 NULL

比较谓词与 IS NULL 谓词

SQL 支持六种比较谓词：<、<=、=、>=、> 和 <>（不等于）。这些用于数值型比较。然而，涉及 NULL 的比较结果总是 UNKNOWN。

示例：

• NULL = 5 评估为 UNKNOWN。
• 5 = NULL 评估为 UNKNOWN。
• NULL <= 5 评估为 UNKNOWN。
• 如果列 col_1 存储 NULL，则 col_1 = 5 评估为 UNKNOWN。

甚至：

• NULL = NULL 也评估为 UNKNOWN。

WHERE 子句 仅返回评估为 TRUE 的行，因此以下查询不会返回列 col_1 为 NULL 的行：

SELECT *
FROM   t1
WHERE  col_1 > 5
   OR  col_1 = 5
   OR  col_1 < 5;

为了检索 NULL 行，需要使用 IS NULL 谓词：

SELECT *
FROM   t1
WHERE  col_1 IS NULL;

其他谓词

对于其他谓词（例如 IN），必须逐一解释。以下是一些关键点：

---

IN 谓词

IN 是 OR 操作的简写。多个值可以写成集合来比较，但如果有 NULL，可能会导致 UNKNOWN。

SELECT *
FROM   t1
WHERE  col_1 IN (2, 5, NULL);

对于包含 NULL 的集合，col_1 = NULL 会导致整个谓词结果为 UNKNOWN。

• 解决方法： 如果需要包含 NULL，需显式处理，如：

SELECT *
FROM   t1
WHERE  col_1 IN (2, 5) OR col_1 IS NULL;

---

GROUP BY 和 NULL

在分组操作中，SQL 将 NULL 视为一个单独的组。例如：

SELECT col_1, COUNT(*)
FROM   t1
GROUP BY col_1;

如果 col_1 包含 NULL，它将单独统计，但显示为 NULL。

---

JOIN 和 NULL

在 JOIN 操作中，如果某一列为 NULL，它不会匹配另一表中的任何值。例如：

SELECT *
FROM   t1
JOIN   t2 ON t1.col_1 = t2.col_x;

解决方法： 可使用 IS NULL 显式处理：

SELECT *
FROM   t1
LEFT JOIN t2 ON t1.col_1 = t2.col_x OR (t1.col_1 IS NULL AND t2.col_x IS NULL);

---

总结：NULL 的三值逻辑

• TRUE：比较结果为真。
• FALSE：比较结果为假。
• UNKNOWN：涉及 NULL 的所有比较。

在实际查询中，必须明确处理 NULL 以避免意外行为。使用 IS NULL 或 IS NOT NULL 是最直接的方式。

问题解析

在 SQL 中，NULL 代表数据缺失，它会影响比较、布尔逻辑运算以及查询结果。以下是一些关于 NULL 的详细说明和示例。

---

NULL 比较的特性

1. NULL 与比较谓词：

   • 比较操作符（=, <, >, <> 等）遇到 NULL 时，结果总是 UNKNOWN。
   • 示例：

     SELECT * 
     FROM t1 
     WHERE col_1 = NULL; -- 始终返回 `UNKNOWN`，无法匹配任何行
     

   • 正确的判断方式是使用 IS NULL：

     SELECT * 
     FROM t1 
     WHERE col_1 IS NULL; -- 检索所有 `col_1` 为 NULL 的行
     

2. IN 谓词与 NULL：

   • IN 是多个 OR 操作的快捷方式，但 IN 无法匹配 NULL。
   • 示例：

     SELECT * 
     FROM t1 
     WHERE col_1 IN (3, 18, NULL); -- NULL 不会命中任何行
     

3. EXISTS 谓词：

   • EXISTS 子查询结果行数大于 0 时返回 TRUE，否则为 FALSE，不会出现 UNKNOWN。
   • 示例：

     SELECT * 
     FROM t1 
     WHERE EXISTS 
       (SELECT * FROM t2 WHERE id < 10); -- 子查询有结果时返回所有行
     

4. LIKE 谓词：

   • 如果列值为 NULL，LIKE 谓词返回 UNKNOWN。
   • 示例：

     SELECT * 
     FROM t1 
     WHERE col_2 LIKE 'Hello %'; -- 不会匹配 `col_2` 为 NULL 的行
     

---

预定义函数与 NULL 的关系

1. 聚合函数：

   • 聚合函数（如 COUNT, SUM, AVG, MIN, MAX）会忽略 NULL 值，但 COUNT(*) 包括所有行。
   • 示例：

     SELECT COUNT(col_1), COUNT(*), AVG(col_1) 
     FROM t1; -- 统计中忽略了 `col_1` 中的 NULL
     

2. 标量函数：

   • 标量函数（如 UPPER(), CONCAT(), ABS()）遇到 NULL 参数时，通常返回 NULL。

---

分组与 NULL

1. DISTINCT 与 GROUP BY：
   • SQL 标准规定 NULL 值不区分，因此在分组中 NULL 值归为一组。
   • 示例：

     SELECT DISTINCT col_1 FROM t1; -- 所有 NULL 只作为一个值
     SELECT col_1, COUNT(*) 
     FROM t1 
     GROUP BY col_1; -- NULL 被归为一组
     

---

三值逻辑（3VL）的布尔运算

1. 布尔检查谓词：

   • SQL 提供了三个额外的谓词：IS [NOT] TRUE, IS [NOT] FALSE, IS [NOT] UNKNOWN。
   • 示例：

     SELECT * 
     FROM t1 
     WHERE (col_1 = col_3) IS UNKNOWN; -- 等价于 `col_1 IS NULL OR col_3 IS NULL`
     

2. 逻辑表：NOT, AND, OR：

p	NOT p	p AND q	p OR q
TRUE	FALSE	见组合逻辑	见组合逻辑
FALSE	TRUE	FALSE	TRUE
UNKNOWN	UNKNOWN	见组合逻辑	见组合逻辑

---

示例

示例 1：简单条件

SELECT *
FROM person
WHERE id = 99 -- TRUE
  AND date_of_birth = NULL; -- UNKNOWN，结果为 NO ROW

示例 2：使用 IS NULL

SELECT *
FROM person
WHERE id = 99 -- TRUE
  AND date_of_birth IS NULL; -- TRUE，结果为 HIT

示例 3：逻辑运算

SELECT *
FROM person
WHERE id = 99 -- TRUE
  OR date_of_birth = NULL; -- TRUE OR UNKNOWN，结果为 HIT

示例 4：嵌套条件

SELECT *
FROM person
WHERE id = 99 -- TRUE
  AND NOT date_of_birth IS NULL; -- TRUE AND FALSE，结果为 NO ROW

---

清理测试数据

DELETE FROM person WHERE id = 99;
DROP TABLE IF EXISTS t1, t2;
COMMIT;