SQL备忘--特殊状态“未知“以及“空值NULL“的判断

一、新逻辑状态：未知

1. 对于大多数其他语言的逻辑判断，一般只有两种结果：真(TURE)或假(FALSE)
2. 但在SQL中，还会有第三种判断结果：未知(UNKNOWN)，表示无法判断出真或者假。

未知状态会影响传统逻辑运算（与或非等）的结果，总结如下：

1. 逻辑与操作

AND/&&	真	假	未知
真	真
假	假	假
未知	未知	假	未知

特别注意：

• 对于AND运算符，只有当两边的运算结果都为真时，最终结果才为真
• 真 AND 未知 = 未知， 并不是真

2. 逻辑或操作

OR	真	假	未知
真	真
假	真	假
未知	真	未知	未知

• 对于OR运算符，只要两边的运算有一个为真时，最终结果就为真，否则最终结果为假或者未知

3. 逻辑非操作

NOT	运算结果
真	假
假	真
未知	未知

二、SQL中"未知"状态的判断影响

SQL语句中的WHERE、HAVING、CASE表达式，只返回逻辑运算结果为真的数据，不返回为假或者未知的数据

三、NULL空值

在数据库中，空值NULL是一个特殊值，表示缺失或者未知
在SQL语句中，任何数据与空值进行算术比较的结果是未知，而非真或非假.。所以空值NULL无法通过 “WHERE c1 = NULL” 来判断，需要写成 WHERE c1 IS NULL ，才能作为条件筛选出查询字段为NULL的数据

1. NULL判断的特殊性

即使两个未知数据进行比较，运算结果也是未知的，比如下面的比较，都得不出TRUE，而是未知。以下例子则判断为未知

NULL = 0
NULL != 0
NULL = ‘’ (空字符串)
NULL = !‘’
NULL = NULL
NULL = !NULL

因此在WHERE语句中进行判断时，务必要注意查询列或者查询条件（即等号两边的数据）都有没有可能为NULL，如果有则要用IS NULL来判定

替代方案

Mysql提供了<=>运算符，即可等值比较，也可空值比较；

-- mysql
SELECT 1 <=> 1, NULL <=> NULL;

PostgreSQL提供的是：IS [NOT] DISTINCT FROM

-- postgreSQL
SELECT 1 IS DISTINCT FROM 1, NULL IS DISTINCT FROM NULL;

2. NULL对IN() 运算符的影响

IN运算符为判断所给条件是否在某个集合中。内部实际使用等值运算符=来判断是否和集合中的每个元素相等，再用OR串联起来得到最后的逻辑结果。

SELECT *
FROM student
WHERE name IN("LiLei", "HanMeimei")		-- 等同于 WHERE name = "LiLei" OR name = "HanMeimei"

因此如果想通过IN() 运算符中加NULL元素来将被查字段中的NULL值也筛选出来，实际是无法生效的。比如想实现以下SQL筛选出学生姓名为NULL的，不会有效

-- 无法筛选出name为NULL的记录
SELECT *
FROM student
WHERE name IN("LiLei", "HanMeimei", NULL)		
/* 等同于 WHERE name = "LiLei" OR name = "HanMeimei" or name = NULL   对于真正name为NULL的数据，此表达式最终的结果为未知，不会被筛选出来   */

在NOT IN() 中使用NULL，影响会更大，使得判断无法筛选出任何记录

-- 无法筛选出任何记录
SELECT *
FROM student
WHERE name NOT IN("LiLei", "HanMeimei", NULL)	
/* 因为原句等同于： WHERE name != "LiLei" and  name != "HanMeimei" and name != NULL. 任何值在最后一句中的判断结果都会为"UNKOWN"，使得整个判断变为未知，被过滤掉 */

3. NULL对子查询语句中，ALL()/ANY() 运算符的影响

子查询中，可以通过比较运算符(=、!=、<、<=、>、>=)与ALL、ANY的组合，来表示等于、不等于、大于…集合中的全部数据

SELECT *
FROM student
WHERE class =ANY (		-- 查找属于1年级的学生SELECT class FROM teacherWHERE grade = 1
)

ALL运算符相当于：对其中每个选项进行比较运算符计算，并用AND运算符串联
IN运算符相当于：对其中每个选项进行=运算符计算，并用OR运算符串联
ANY与IN类似，也是由OR运算符串联，比较运算符写于ANY之前；如果是=ANY，则与IN相同

对于ALL、ANY等运算符，后面加上NULL不会成功筛选出想要的NULL数据，相反会导致比较离谱的运算结果

总结

• 使用IN/NOT IN/ALL/ANY时，切记不要在选项中设置NULL。对于子查询做以上匹配结果时，也要注意务必过滤下NULL数据

4. 空值处理

COALESCE函数

COALESCE(exp1, exp2, exp3, …)接收一个输入列表，返回第一个非NULL的参数；若都为空，则返回NULL

SELECT COALESCE(yuwen_score, shuxue_score, yingyu_score)
FROM student

可以用COALESCE将NULL转换为别的默认值，类似于CASE WHEN

SELECT COALESCE(yuwen_score, 0)   -- 若语文成绩为NULL， 则记为0分
FROM student

NULLIF函数

NULLIF(exp1, exp2)接收两个入参：若相等则返回NULL；若不等则返回exp1

SELECT NULLIF(yuwen_score, 0)   -- 若语文成绩为0， 则记为NULL；不为0，则取此成绩
FROM student

NULLIF函数最大的目的是被用来防止除零错误

SELECT AVG(yuwen_score)/NULLIF(yuwen_score, 0) --若某同学语文成绩为0，则分母为NULL（不是0），此时不会报错
FROM student

IFNULL函数

MYSQL与SQLite才有，入参只有两个，功能是返回两个入参中第一个非空的值（可视为入参固定为两个的COALESCE函数）。注意与NULLIF区分。

SELECT IFNULL(yuwen_score, 0)   -- 若语文成绩为NULL， 则记为0
FROM student