比较操作符 with null 任何值和NULL使用比较运算符(>,<,>=,<=,<>,=)或者(in, not in, any/some, all)比较时,返回值都为NULL
in / not in with null
select*from test;+------+------+ | a | b |+------+------+ |1|1||1|NULL||NULL|NULL|+------+------+
mysql>select*from test1 where a in(null);
Empty set(0.00 sec)
mysql>select*from test1 where a in(null,1);+------+------+ | a | b |+------+------+ |1|1||1|NULL|+------+------+ 2rowsinset(0.00 sec)-- 当in和null比较时,无法查询出为null的记录
mysql>select*from test1 where a notin(1);
Empty set(0.00 sec)
mysql>select*from test1 where a notin(null);
Empty set(0.00 sec)
mysql>select*from test1 where a notin(null,2);
Empty set(0.00 sec)
mysql>select*from test1 where a notin(2);+------+------+ | a | b |+------+------+ |1|1||1|NULL|+------+------+ 2rowsinset(0.00 sec)
-- 当not in后面有null值时,不论什么情况下,整个sql的查询结果都为空
min/ max with null
sum/ count with null sum的列中有null时返回null; count的列中有null时不计数
order by with null null 会参与排序,且被当成是无穷小的数参与排序
window function with null 同上,且针对rank 和 dense_rank null值会被赋予同样的排序值
比较操作符 with null任何值和NULL使用比较运算符(>,<,>=,<=,<>,=)或者(in, not in, any/some, all)比较时,返回值都为NULLin / not in with nullselect * from test;+------+------+ | a | b | +------+------+ | 1 | 1 | | 1 | NULL | | NULL | NULL | +-.