一、null的特点

1、null来表示未知和不确定。

null 在计算机和编程世界中表示的是未知,不确定。虽然中文翻译为“空”, 但此空(null)非彼空(empty)。null表示的是一种未知状态，未来状态,比如小明兜里有多少钱我不清楚，但也不能肯定为0，这时在计算机中就使用null来表示未知和不确定。

2、null不支持大小/相等判断

null表示的是什么都没有，它与空字符串、0 这些是不等价的，是不能用于比较的！ 如： 0 = null 、null = '' 、null>3、null!='hehe'、null<10、age not in (10,20,null)、a!='null'得到的结果为 false。

正因为以上原因，要少用null。

一方面不利于代码的可读性和可维护性，特别是强类型语言，查询 INT 值，结果得到一个 NULL，程序可能会奔溃...如果要兼容这些情况程序往往需要多做很多操作来兜底

另一方面若所在列存在 null值，会影响 count()、 <col> != <value>、null + 1 等查询、统计、运算情景的结果。

二、null的查询和null相关函数

1、将某个值与 null 进行比较-is null\is not null\isnull()\ is distinct from\ is not distinct from

1) is null\is not null\isnull()—判断是否为空

一般用在where判断中，返回符合条件的行，例如：

select name,age,class from person where name is null
select name,age,class from person where name is not null
select name,age,class from person where name isnull(name)

还可以和if结合作为条件判断重构字段，例如

select name,if(age is null,0,age) age from person where name is null
select name,if(age is not null,age,0) age from person where name is not null
select name,if(age isnull(name),0,age) age from person where name 

和if联用的时候，类似于ifnull函数

2) is distinct from\ is not distinct from—判断两列值是否不同\相同

其中，

is not distinct from，A和B的数据类型、值不完全相同返回 false A和B的数据类型、值完全相同返回 true，将空值视为相同。

is distinct from，A和B的数据类型、值不完全相同返回 true A和B的数据类型、值完全相同返回 false，将空值视为相同。

select * from users  where age1 is distinct from age2
select * from users  where age1 is  not distinct from age2

若使用版本sql不支持该写法，可重写为

a is distinct from b <==> coalesce(a, placeholder) <> coalesce(b, placeholder)
a  is not distinct from b <==>coalesce(a, placeholder) = coalesce(b, placeholder)

2、ifnull（）,两参数，若第一个参数不为空，返回该字段，若为空，返回第二个参数

它的用法相当于是判断出age为空时用什么替代它。

select name,ifnull(age,0) age from person

3、coalesce（），多参数，返回第一个非空值

select name,coalesce(age,0) age from person

以上结果会等同ifnull。

但是coalesce还可以加很多参数，返回第一个非空参数，例如coalesce(null,null,4,null,3,1)会返回4。

select name,coalesce(age,class,0) age from person
select name,coalesce(age,1,0) age from person

注意：如果coalesce()中所有参数都为空，如coalesce(null,null,null)，那么会报错。

4、nullif()，该函数接受两个参数，相等则返回null，不相等则返回第一个参数

nullif()虽然和ifnull（）长得像，但功能有些区别，一般用来防止除零错误，例如：

select sum/num

可以转换为

select sum/nullif(num,0)

这样num为0时会返回null，而不是错误

如果不想要 null，而是希望转换为 0 或者其他数， 则可以在前一个SQL的基础上使用 coalesce函数

select coalesce(sum/nullif(num,0), 0)

5、not in 与 Null

select * from users 
where age not in (select age from class)

如果class表中某一行的 age 是 null 的话，返回结果是空的，因为它的查询如下。

select * from users 
where age not in (21, 24,13,18, null)
这个SQL语句会被转换为:
select * from users 
where age != 1 and age != 2 and age != null

id != null 结果是个未知值, null，而任意值和 null 进行 and 运算的结果都是 null, 所以相当于没有其他条件。那么出这种结果的原因就是 null 的逻辑值不为 true。

注意：age in（21,24,13,18,null）时会忽略null，非空查询才会出错。

6、null与排序

在mysql中认为null在排序时为最小值，即ASC排序时在最前面，DESC排序时在最后，oracle中认为null在排序时为最大值，即ASC排序时在最后面，DESC排序时在最前，mysql中可采用以下方法转换null为最大值(未穷举)。

order by -age desc
order by case when age is null then 1 else 0 end asc,age asc 
order by IF(ISNULL(age),1,0),age

7、null与聚合函数

count(*)时，输出的是所有记录行数，包括列全为null的行。countvalue具体某个字段时，如count(value)不会统计null。

sum(value)忽视null。avg视为sum(value)/count(value)，忽略null

题外话：空字符问题—一般sum忽略空字符，count不忽略。