NULL的条件运算 与 值运算

• 在SQL标准中，NULL的含义是“unknown”，用于适配一些拿不准的场景or确实不知道数据
• 在SQL标准中，条件运算（WHERE/ON/HAVING IF/CASE WHEN）的逻辑结果状态有三种：true、false、unknown
• 以上条件运算如果判断为true（当且仅当条件运算结果为true，其他都不行），则可以选择这一条数据

1.条件运算符

null（unknown）与任何一个value进行条件运算，都会返回unknown，而不会返回true，因此如下几种 null 相关的条件运算都不会把对应的那行数据筛选出来

unknown比unknown

unknown比value

2.值运算符

加减乘除

3.条件运算对NULL的正确用法

3.1 IS NULL

3.2 IS NOT NULL

3.3 isnull()函数

函数随便放在哪个位置，这个函数的作用就是如果null，则取一个预定值

4.例题

4.1正确写法

• SELECT name FROM customer WHERE referee_id <> 2 OR referee_id IS NULL;

• SELECT name FROM customer WHERE referee_id != 2 OR referee_id IS NULL;

• 下面这是一个补集思想的写法
  select name from customer
  where id not in (select id from customer where referee_id=2)

4.2错误写法

这样仍然筛选不出来referee_id 为 NULL对应的数据

• SELECT name FROM customer WHERE referee_id = NULL OR referee_id <> 2;

5.建表约束建议

定义表结构时, 尽量使用 NOT NULL 约束

• 字符串类型必须非空约束, 以空串代表空值（防止产生歧义，如where title != xxx筛不出title为null的）
• 布尔值类型必须非空约束, 默认 true 或 false（防止产生歧义
  ）
• 数字 / 时间类型能用非空约束就用非空约束（时间作为筛选条件产生歧义的场景相对较少）

6.gourp by

例如有如下语句

SELECT stu_name , SUM(phone) FROM students GROUP BY stu_name;

其中stu_name有的值为null

那么

• 所有聚合如SUM/AVG/MAX/MIN/COUNT都会把所有NULL作为一组

7.order by

SELECT * FROM students ORDER BY stu_name;

无论正序倒序，所有NULL属性的值排在一起

8.总结

• 建表的时候尽可能用NOT NULL约束字段
• 对于有null值的字段，条件运算时一定要考虑unknown的情况，分情况使用IS NULL、IS NOT NULL、isnull()函数
• boolean字段如果有null值，用IS TRUE、IS NOT TRUE来避免unknown的情况
• gourp by时所有null分为一组
• order by时所有null挨在一起