mysql之-三值逻辑和null

三值逻辑基础

1、两种null类型

• 未知NULL：某个属性现在不知道，但是真实存在的，添加某些限定条件之后可以知道。
• 不适用NULL:某个属性值不存在，即使添加条件也不知道。

例如：对于有头发这个属性，对象为冰箱时就是不适合，因为冰箱不可能存在头发这个属性。当对象为人时，这个属性存在，当摘下帽子就可知道了。

2. 为什么当我们用where语句判断某个值是否为空时，要是用“is null”而不是“=null”

因为对null比较时使用谓词（= > < !=等）返回的结果是unknown，而使用where筛选出来的都是判断结果为true的行，所以使用“=null”无法显示出包含null的行。

对null比较时使用谓词得到结果不能为真是因为，null既不是值也不是变量，而比较谓词只适用于值。

3. unknown、第三个真值

sql三值逻辑中的三种值：true,false,unknown
三值逻辑的真值表

• NOT

• AND

false ＞?unknown ＞?true

• OR

true ＞?unknown ＞?false

应用

1. 当需要作为筛选条件中的列包含null则where条件筛选时需要添加is[not]null
   eg:如果表格Students 的age列包含null，则需要添加age IS NULL

SELECT *
FROM Students
WHERE age = 20
OR age <> 20
OR age IS NULL;

2. 比较谓词和null：case 表达式和null
   eg:col_1 为 1 时返回○、为 NULL 时返回 × 的 CASE 表达式？

CASE col_1
WHEN 1 THEN ‘○‘
WHEN NULL THEN ‘ב
END

一定不会返回null，因为WHEN NULL相当于col_1 = NULL 的缩写形式，真值永远为unknown。以下为正确的写法

CASE WHEN col_1 = 1 THEN ‘○‘
WHEN col_1 IS NULL THEN ‘ב END

3. NOT IN 和 NOT EXISTS 不是等价的 (IN 和 EXISTS 可以互相替换使用当 NOT IN 改写成 NOT EXISTS 时需要注意）
   eg:查询“与 B班住在东京的学生年龄不同的 A 班学生”
   
   

• 用not in

SELECT *
FROM Class_A
WHERE age NOT IN
( SELECT age
FROM 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 语句整体的查询结果永远 是空

• 用not exists

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

过程详解：

--1. 在子查询里和 NULL 进行比较运算
SELECT *
FROM Class_A A
WHERE NOT EXISTS
( SELECT *
ROM 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;

4. 限定谓词ALL和 NULL

-- 查询比 B 班住在东京的所有学生年龄都小的 A 班学生
SELECT *
FROM Class_A
WHERE age < ALL
( SELECT age
FROM Class_B
WHERE city = ‘东京‘ );

当B中有人年龄不详时查询结果为空。因为ALL 谓词其实是多个以 AND 连接的逻辑表达式的省略写法。所以子查询中有null时where后的值不为true。

5. 限定谓词和极值函数不是等价的
   这是因为，极值函数在统计时会把为NULL的数据排除掉。但当子查询中没有满足条件的行时，子查询中使用极值函数max、min时返回结果为null，即主查询查不出任何结果，使用all时会返回true，主查询会返回所有的值。

6.聚合函数和 NULL
当子查询输入为空表时返回null不仅有极值函数，还有count等聚合函数，所以外侧查询也返回不了任何值。

注：参考文献 sql进阶教程

mysql之-三值逻辑和null

原文：https://www.cnblogs.com/DA-fighting/p/12680231.html