读SQL进阶教程笔记02_三值逻辑和NULL

 

1. 三值逻辑

1.1. 真

• 1.1.1. true

1.2. 假

• 1.2.1. false

1.3. 不确定

• 1.3.1. unknown

2. 两种NULL

2.1. 未知”（unknown）

• 2.1.1. 不知道戴墨镜的人眼睛是什么颜色

• 2.1.2. 虽然现在不知道，但加上某些条件后就可以知道

2.2. “不适用”（not applicable,inapplicable）

• 2.2.1. 不知道冰箱的眼睛是什么颜色

• 2.2.2. 在语义上更接近于“无意义”

• 2.2.3. 而不是“不确定”

• 2.2.4. 无论怎么努力都无法知道

2.3. Codd提倡在关系数据库中使用四值逻辑

• 2.3.1. 现在所有的DBMS都将两种类型的NULL归为了一类并采用了三值逻辑

2.4. 数据库里只要存在一个NULL，查询的结果就可能不正确

2.5. 最佳方法应该是往表里添加NOT NULL约束来尽力排除NULL

3. IS NULL

3.1. 谓词

3.2. 对NULL使用比较谓词后得到的结果总是unknown

• 3.2.1. --以下的式子都会被判为 unknown

     1 = NULL
     2 ＞ NULL
     3 ＜ NULL
     4 ＜＞ NULL
     NULL = NULL

• 3.2.2. 比较谓词只适用于值

  • 3.2.2.1. NULL不是值，所以不能对其使用谓词

3.3. 查询结果只会包含WHERE子句里的判断结果为true的行

• 3.3.1. 不会包含判断结果为false和unknown的行

3.4. NULL既不是值也不是变量

• 3.4.1. NULL只是一个表示“没有值”的标记

• 3.4.2. 因为NULL不是值，所以不在定义域（domain）中

3.5. IS NULL这样的谓词是由两个单词构成的，所以人们容易把IS当作谓词，而把NULL当作值

4. 三值逻辑

4.1. AND的情况： false ＞ unknown ＞ true

• 4.1.1. 优先级

4.2. OR的情况： true ＞ unknown ＞ false

• 4.2.1. 优先级

5. 排中律

5.1. Law of Excluded Middle

5.2. “把命题和它的否命题通过‘或者’连接而成的命题全都是真命题”这个命题在二值逻辑中被称为排中律

5.3. 在SQL的世界里，排中律是不成立的

5.4. 示例

• 5.4.1. --查询年龄是20岁或者不是20岁的学生

     SELECT ＊
       FROM Students
      WHERE age = 20
         OR age ＜＞ 20;

• 5.4.2. --1．约翰年龄是NULL（未知的NULL！

     SELECT ＊
       FROM Students
      WHERE age = NULL
         OR age ＜＞ NULL;

• 5.4.3. --2．对NULL使用比较谓词后，结果为unknown

     SELECT ＊
       FROM Students
      WHERE unknown
         OR unknown;

• 5.4.4. --3.unknown OR unknown的结果是unknown

     SELECT ＊
       FROM Students
      WHERE unknown;

• 5.4.5. SQL语句的查询结果里只有判断结果为true的行

  • 5.4.5.1. --添加第3个条件：年龄是20岁，或者不是20岁，或者年龄未知

     SELECT ＊
       FROM Students
      WHERE age = 20
         OR age ＜＞ 20
         OR age IS NULL;

6. CASE表达式

6.1. 示例

• 6.1.1. --col_1为1时返回○、为NULL时返回×的CASE表达式？

     CASE col_1
       WHEN 1     THEN'○'
       WHEN NULL  THEN'×'
     END

• 6.1.2. CASE表达式一定不会返回×

  • 6.1.2.1. 第二个WHEN子句是col_1 = NULL的缩写形式

• 6.1.3. 

  CASE WHEN col_1 = 1 THEN'○'
  
         WHEN col_1 IS NULL THEN'×'
      END

6.2. CASE表达式的判断方法与WHERE子句一样，只认可真值为true的条件

7. NOT EXISTS

7.1. IN改写成EXISTS是等价改写

7.2. NOT IN和NOT EXISTS不是等价的

7.3. 示例

• 7.3.1. B班山田的年龄是NULL

• 7.3.2. --查询与B班住在东京的学生年龄不同的A班学生的SQL语句？

     SELECT ＊
       FROM Class_A
      WHERE age NOT IN ( SELECT age
                           FROM Class_B
                         WHERE city =’东京’);

  • 7.3.2.1. 结果是空，查询不到任何数据

• 7.3.3. --正确的SQL语句：拉里和伯杰将被查询到

     SELECT ＊
       FROM Class_A  A
      WHERE NOT EXISTS ( SELECT ＊
                           FROM Class_B B
                         WHERE A.age = B.age
                           AND B.city = ’东京’);

• 7.3.4. --1．执行子查询，获取年龄列表

     SELECT ＊
       FROM Class_A
      WHERE age NOT IN (22, 23, NULL);

  • 7.3.4.1. --1．在子查询里和NULL进行比较运算

     SELECT ＊
       FROM Class_A A
      WHERE NOT EXISTS ( SELECT ＊
                           FROM Class_B B
                         WHERE A.age = NULL
                           AND B.city =’东京’);

• 7.3.5. --2．用NOT和IN等价改写NOT IN

     SELECT ＊
       FROM Class_A
      WHERE NOT age IN (22, 23, NULL);

  • 7.3.5.1. --2．对NULL使用“=”后，结果为 unknown

     SELECT ＊
       FROM Class_A A
      WHERE NOT EXISTS ( SELECT ＊
                           FROM Class_B B
                         WHERE unknown
                           AND B.city =’东京’);

• 7.3.6. --3．用OR等价改写谓词IN

     SELECT ＊
       FROM Class_A
      WHERE NOT ( (age = 22) OR (age = 23) OR (age = NULL) );

  • 7.3.6.1. --3．如果AND运算里包含unknown，结果不会是true

     SELECT ＊
       FROM Class_A A
      WHERE NOT EXISTS ( SELECT ＊
                           FROM Class_B B
                         WHERE false或unknown);

• 7.3.7. --4．使用德·摩根定律等价改写

     SELECT ＊
       FROM Class_A
      WHERE NOT (age = 22) AND NOT(age = 23) AND NOT (age = NULL);

  • 7.3.7.1. --3．如果AND运算里包含unknown，结果不会是true

     SELECT ＊
       FROM Class_A A
      WHERE NOT EXISTS ( SELECT ＊
                           FROM Class_B B
                         WHERE false或unknown);

  • 7.3.7.2. 山田被作为“与任何人的年龄都不同的人”来处理了

• 7.3.8. --5．用＜＞等价改写NOT和=

     SELECT ＊
       FROM Class_A
      WHERE (age ＜＞ 22) AND (age ＜＞ 23) AND (age ＜＞ NULL);

• 7.3.9. --6．对NULL使用＜＞后，结果为unknown

     SELECT ＊
       FROM Class_A
      WHERE (age ＜＞ 22) AND (age ＜＞ 23) AND unknown;

• 7.3.10. --7．如果AND运算里包含unknown，则结果不为true

     SELECT ＊
       FROM Class_A
      WHERE false或unknown;

7.4. EXISTS谓词永远不会返回unknown

• 7.4.1. EXISTS只会返回true或者false

8. ALL谓词

8.1. 多个以AND连接的逻辑表达式的省略写法

8.2. 与IN是等价的，所以我们不经常使用ANY

8.3. 示例

• 8.3.1. --查询比B班住在东京的所有学生年龄都小的A班学生

     SELECT ＊
       FROM Class_A
      WHERE age ＜ ALL ( SELECT age
                         FROM Class_B
                         WHERE city =’东京’);

  • 8.3.1.1. 他的年龄比在东京住的所有学生都小

• 8.3.2.

   --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;

9. 极值函数

9.1. --查询比B班住在东京的年龄最小的学生还要小的A班学生

    SELECT ＊
      FROM Class_A
     WHERE age ＜ ( SELECT MIN(age)
                    FROM Class_B
                    WHERE city =’东京’);

• 9.1.1. 他的年龄比在东京住的年龄最小的学生还要小

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

9.3. 极值函数在统计时会把为NULL的数据排除掉

9.4. 极值函数在输入为空表（空集）时会返回NULL

• 9.4.1.

   --1．极值函数返回NULL
     SELECT ＊
       FROM Class_A
      WHERE age ＜ NULL;
     --2．对NULL使用“＜”后结果为 unknown
     SELECT ＊
       FROM Class_A
      WHERE unknown;

10. 聚合函数

10.1. COUNT以外的聚合函数当输入为空表时返回NULL

10.2. 示例

• 10.2.1. --查询比住在东京的学生的平均年龄还要小的A班学生的SQL语句？

     SELECT ＊
       FROM Class_A
      WHERE age ＜ ( SELECT AVG(age)
                     FROM Class_B
                     WHERE city =’东京’);

• 10.2.2. 没有住在东京的学生时，AVG函数返回NULL

• 10.2.3. 外侧的WHERE子句永远是unknown，也就查询不到行