SQL中这些与NULL有关的细节，你知道吗？

点击上方“超哥的杂货铺”，轻松关注

NULL是SQL常见的关键字之一，表示“空，无”的意思。它在SQL中是一种独特的存在，今天来汇总一下与它相关的知识点，看看这些你都知道吗？

---

先贴一下我们的原始数据，是一个只有1列的表，表名为example，很简单：

1.NULL是一种特殊的值，对某字段使用distinct 关键字时，NULL和一般值一样，都会排重，只保留一个值。

2.不能对NULL值使用比较运算符

直白地讲，不能对null值使用等号(=)或者不等号(!=)进行比较，要使用is null 和 is not null。

有一种情况需要注意，假设我们需要取col不为2的所有col值，包括null。不能只写where col <> '2'，因为这样的写法不会包括NULL值。我们需要写成where col <> '2'  or col is null。

3.count(*)会统计null值，count(列名)不包括null值。

4.含NULL值的运算结果都为NULL，如下面图所示(点击查看大图)

5.使用sum函数和avg函数时，相应列中包含NULL的，会发生什么？

sum和avg函数作用于含有NULL的列，NULL值不参与计算。上面图中，sum(col) 是1+2+2+3=8。avg(col) 是(1+2+2+3)/4=2，注意分母是4而不是6。如果需要将NULL值当作0值参与到运算中，可以用case when的方式进行判断赋值。

select sum(case when col is null then 0 else col end) from example;#结果是8
select avg(case when col is null then 0 else col end) from example;#分母是6，结果是1.33

除此外，在使用max，min时，也会忽略NULL值。事实上，聚合函数如果以列名为参数，那么在计算之前就会把NULL 排除在外。

6.如果某列含有null，使用group by 进行聚合时，null值会单独保留一行。

这一点和第一点有点类似，见下面代码。

7.null占用的空间是多少？

我们在原数据的基础上插入了一行空字符串的数据。然后来看每一个值所占用的空间。可以看到，NULL所占的空间是NULL，是占用空间的，而空字符串长度是0，是不占用空间的。

NULL columns require additional space in the row to record whether their values are NULL.

NULL列需要行中的额外空间来记录它们的值是否为NULL。

有一个比喻很恰当：空值就像是一个真空状态杯子，什么都没有，而NULL值就是一个装满空气的杯子，虽然看起来都是一样的，但是有着本质的区别。

补充说明：对于空值的判断需要用=，!= 等算数运算符，而NULL值不行。count等聚合函数会忽略NULL值，但不会忽略空值。

8.对NULL进行排序，结果如何？

上面的结果，升序排序，NULL在最开头，但这并不能说明NULL比1小，因为我们前面提到是不能对NULL使用比较运算符的。这里的结果只是把NULL放在了开头显示，可能在另外的数据库中，会统一放到结尾显示。

9.大多数函数作用于NULL，结果都是NULL，如concat函数，abs函数等。但COALESCE函数除外，它返回第一个不为NULL的值。我们常会看到的NVL函数是该函数的简化版本，类似的函数还有IFNULL。

SELECT COALESCE(NULL, 1) AS col_1,
COALESCE(NULL, 'test', NULL) AS col_2,
COALESCE(NULL, NULL, '2009-11-01') AS col_3;
--结果：1 test 2009-11-01

10.NULL的其他作用

NULL多用在字段约束中，如非空约束可以用NOT NULL表示。NULL经常用在case表达式中的ELSE子句中：case when <条件>  else NULL  end，else的部分也可以不写，但为了易读性，还是建议写。

小结

对NULL常用的知识点总结如下面的思维导图，欢迎大家补充。(在有些地方看到了在插入和更新数据时NULL的注意事项和索引相关的知识，由于用的比较少，就不放在这里了，可以参考文末链接自行学习)

reference:

1.《SQL基础教程》

2.https://www.cnblogs.com/aflyun/p/11125756.html

后台回复“NULL”，获取本文PDF版本和思维导图源文件。

以清净心看世界；

用欢喜心过生活。

超哥的杂货铺，你值得拥有~

长按二维码关注我们

往期精选

 

在一个机器学习项目中，你需要做哪些工作？

图解pandas模块21个常用操作

一场pandas与SQL的巅峰大战（七）

一场pandas与SQL的巅峰大战（六）

图解pandas模块21个常用操作