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

一、新逻辑状态：未知

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

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

1. 逻辑与操作

特别注意：

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

2. 逻辑或操作

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

3. 逻辑非操作

二、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 teacher
	WHERE 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

到此这篇关于SQL 特殊状态“未知“以及“空值NULL“的判断的文章就介绍到这了,更多相关SQL 未知状态及“空值NULL内容请搜索程序员之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持程序员之家！ 

最新评论