MICK-SQL进阶教程 1.3 三值逻辑和NULL

2020-03-21 本文已影响0人大美mixer

让自己愈发觉得自己学艺不精的一章、、、

要点：

三值逻辑：true, false, unknown
必须写成“IS NULL”，而不是“= NULL”：对 NULL 使用比较谓词后得到的结果总是 unknown。而查询结果只会包含 WHERE 子句里的判断结果为 true 的行，不会包含判断结果为 false 和 unknown 的行。
NULL 既不是值也不是变量。NULL 只是一个表示“没有值”的标记，而比较谓词只适用于值。因此，对并非值的 NULL 使用比较谓词本来就是没有意义的。

真值 unknown 和作为 NULL 的一种的 UNKNOWN(未知)是不同的东西。前者是明确的布尔型的真值，后者既不是值也不是变量。为了便于区分，前者采用粗体的小写字母 unknown，后者用普通的大写字母 UNKNOWN 来表示。
真值的优先级排序
- AND 的情况: false > unknown > true
- OR 的情况: true > unknown > false

比较谓词和 NULL(1) :排中律不成立

比较谓词和 NULL(2) :CASE 表达式和 NULL

-- col_1 为 1 时返回○、为 NULL 时返回 × 的 CASE 表达式? 
CASE col_1
WHEN 1 THEN '○'
WHEN NULL THEN '×' END
-- 这个 CASE 表达式一定不会返回 ×。这是因为，第二个 WHEN 子句 是 col_1 = NULL 的缩写形式。正如大家所知，这个式子的真值永 远是 unknown。而且 CASE 表达式的判断方法与 WHERE 子句一样，只 认可真值为 true 的条件。
-- 正确的写法是像下面这样使用搜索 CASE 表 达式。
CASE WHEN col_1 = 1 THEN '○' 
         WHEN col_1 IS NULL THEN '×'
END

NOT IN 和 NOT EXISTS 不是等价的

在对 SQL 语句进行性能优化时，经常用到的一个技巧是将 IN 改写成 EXISTS。这是等价改写，并没有什么问题。问题在于，将 NOT IN 改写成NOT EXISTS时，结果未必一样。

-- 选择“与 B 班(class_b,包含空值)住在东京的学生年龄不同的 A 班(class_a)学生”
-----------------------------------------------------
-- 错误写法
select name
from class_a
where age not in (select age 
                  in class_b 
                  where city = '东京');
-- 结果：空
-----------------------------------------------------
-- 执行原理
  --1. 执行子查询，获取年龄列表 
  SELECT *
  FROM Class_A
  WHERE age NOT IN (22, 23, NULL);
  --2.用NOT和IN等价改写NOT IN 
  SELECT *
  FROM Class_A
  WHERE NOT age IN (22, 23, NULL);
  --3. 用 OR 等价改写谓词 IN 
  SELECT *
  FROM Class_A
  WHERE NOT ( (age = 22) OR (age = 23) OR (age = NULL) );
  --4. 使用德 · 摩根定律等价改写 
  SELECT *
  FROM Class_A
  WHERE NOT (age = 22) AND NOT(age = 23) AND NOT (age = NULL);
  --5.用<>等价改写 NOT和 = 
  SELECT *
  FROM Class_A
  WHERE (age <> 22) AND (age <> 23) AND (age <> NULL);
  --6. 对 NULL 使用 <> 后，结果为 unknown 
  SELECT *
  FROM Class_A
  WHERE (age <> 22) AND (age <> 23) AND unknown;
  --7.如果 AND 运算里包含 unknown，则结果不为 true 
  SELECT *
  FROM Class_A
  WHERE false或unknown;
-----------------------------------------------------

也就是说，如果 NOT IN 子查询中用到的表里被选择的列中存在 NULL，则 SQL 语句整体的查询结果永远是空。

-- 正确写法
select name
from class_a
where age not exists (select age 
                        in class_b 
                        where city = '东京');
-----------------------------------------------------
-- 原理
    --1. 在子查询里和NULL进行比较运算
    select *
  from Class_A A
  where not exists (select * 
                    from Class_B B 
                    where A.age = NULL 
                      and B.city = '东京');
  --2.对NULL使用“=”后，结果为 unknown
  SELECT *
  FROM Class_A A
  WHERE NOT EXISTS (SELECT *
                                    FROM Class_B B 
                    WHERE unknown AND B.city = '东京')
  --3. 如果 AND 运算里包含 unknown，结果不会是 true
  SELECT *
    FROM Class_A A
    WHERE NOT EXISTS ( SELECT *
                     FROM Class_B B
                     WHERE false或unknown);
  --4.子查询没有返回结果，因此相反地，NOT EXISTS为true 
  SELECT *
  FROM Class_A A WHERE true;
-----------------------------------------------------

产生这样的结果，是因为 EXISTS 谓词永远不会返回 unknown。EXISTS 只会返回 true 或者 false。因此就有了 IN 和 EXISTS 可以互相替换使用，而 NOT IN 和 NOT EXISTS 却不可以互相替换的混乱现象。

限定谓词和 NULL

SQL 里有 ALL 和 ANY 两个限定谓词。因为 ANY 与 IN 是等价的，所以我们不经常使用 ANY。ALL 可以和比较谓词一起使用，用来表达“与所有的 ×× 都相等”，或“比所有的 ×× 都大”的意思。

-- 查询比 B 班住在东京的所有学生年龄都小的 A 班学生
-----------------------------------------------------
-- b表中不含Null时
SELECT *
FROM Class_A
WHERE age < ALL ( SELECT age
                  from Class_B
                  WHERE city = '东京')
-- 结果：正确
-----------------------------------------------------
-- b表中含Null时
-- 结果：空
-- 原理：ALL 谓词其实是多个以 AND 连接的逻辑表达式的省略写法。
  --1. 执行子查询获取年龄列表 
  SELECT *
  FROM Class_A
  WHERE age < ALL ( 22, 23, NULL )
  --2. 将 ALL 谓词等价改写为 AND 
  SELECT *
  FROM Class_A
  WHERE (age < 22) AND (age < 23) AND (age < NULL);
  --3.对NULL使用“<”后，结果变为 unknown 
  SELECT *
  FROM Class_A
  WHERE (age < 22) AND (age < 23) AND unknown;
  --4. 如果AND运算里包含unknown，则结果不为true 
  SELECT *
  FROM Class_A
  WHERE false 或 unknown;

限定谓词和极值函数不是等价的

极值函数在统计时会把为 NULL 的数据排除掉
极值函数在输入为空表(空集)时会返回 NULL

ALL 谓词和极值函数表达的命题含义分别如下所示：

ALL 谓词：他的年龄比在东京住的所有学生都小
极值函数：他的年龄比在东京住的年龄最小的学生还要小

命题不等价的情况：

表里存在 NULL 时它们是不等价的
谓词(或者函数)的输入为空集的情况（例如B表中没有学生住东京）：

这时，使用 ALL 谓词的 SQL 语句会查询到 A 班的所有学生。然而，用极值函数查询时一行数据都查询不到。因为极值函数在输入为空表(空集)时会返回 NULL

-- 查询比 B 班住在东京的年龄最小的学生还要小的 A 班学生 
SELECT *
FROM Class_A
WHERE age < ( SELECT MIN(age)
                            FROM Class_B
                            WHERE city = '东京' );
-- 原理
--1. 极值函数返回 NULL 
SELECT *
FROM Class_A 
WHERE age < NULL;
--2.对NULL使用“<”后结果为 unknown 
SELECT *
FROM Class_A 
WHERE unknown;

比较对象原本就不存在时，根据业务需求有时需要返回所有行，有时需要返回空集。需要返回所有行时(感觉这类似于“不战而胜”)，需要使用 ALL 谓词，或者使用 COALESCE 函数将极值函数返回的 NULL 处理成合适的值。

聚合函数和NULL

聚合函数在输入为空表(空集)时会返回 NULL

-- 查询比住在东京的学生的平均年龄还要小的 A 班学生的 SQL 语句? 
SELECT *
FROM Class_A
WHERE age < ( SELECT AVG(age)
              FROM Class_B
              WHERE city = '东京' );
-- 没有住在东京的学生时，AVG 函数返回 NULL。

本节要点

NULL 不是值。
因为 NULL 不是值，所以不能对其使用谓词。
对 NULL 使用谓词后的结果是 unknown。
unknown 参与到逻辑运算时，SQL 的运行会和预想的不一样。
按步骤追踪 SQL 的执行过程能有效应对 4 中的情况。

要想解决 NULL 带来的各种问题，最佳方法应该是往表里添加NOT NULL约束来尽力排除NULL。