sql指南之null值用法

注明：参考文章：

SQL避坑指南之NULL值知多少？_select null as-CSDN博客文章浏览阅读2.9k次，点赞7次，收藏21次。0 引言 SQL NULL（UNKNOW）是用来代表缺失值的术语，在表中的NULL值是显示为空白字段的值，用作不知道数据的具体值，或者不知道数据是否存在，或者数据不存在等情况。NULL值在SQL中普遍存在，想必大多数程序员对其有所了解，但是对于NULL值在SQL中贯穿体现及相关细节，是否会有人注意到呢？本文将对SQL中NULL值的一些使用细节进行研究，让你在使用过程中避开NULL值的坑，..._select null ashttps://blog.csdn.net/godlovedaniel/article/details/104860042

0 概要

   sql null(unknow) 是用来代表缺失值或无意义的术语，表中的null值显示为空白字段的值。

1 null值用途

（1）被定义为缺失值
（2）被定义为无意义的值
（3）不存在的值
（4）不确定性值
（5）建表语句中作为非空约束

2 数据准备

create table if not exists  table_null
(
    id    int comment 'id',
    name  string comment '名称'
) comment 'null值测试表';
 
insert overwrite table table_null values
(1,'吱吱'),
(2,'嘎巴'),
('',''),
(4,''),
(null,null),
(6,null),
(null,'');

3 null使用场景

3.1 null值的比较

对null进行判断时，只能用is null或is not null，不能采用 =, <, <>, != 等这些操作符

例如经过如下操作：

select * from table_null where id <> '1';

输出结果为：

  该条语句正常返回应该是6条数据，但是结果只有3条，说明id为null的值并没有返回，null的判断有其特殊的语法。接下来，进行校验：

select * from table_null where id <> '1' or id is null;

 查看结果是：

此时结果才是符合预期。

一般筛选出name不为null，有两种sql表示方法：

第一种：

select * from table_null where name != null;

没报错但结果明显不对

第二种：

select * from table_null where name is not null;

 可以看到结果符合预期。

3.2 null与聚合函数的运算

1.count(*)操作时会统计null值，count(column)会过滤掉null值;
2.事实上除了count(*)计算，剩余的聚合函数例如： max(column),min(column),avg(column),count(column) 函数会过滤掉null值

3.3 null值参与算数运算

与null值进行算数运算时，其操作结果都是null

(1) select 1+ null
(2) select 1*null
(3) select 1/null
(4) select null*0

3.4 null值参与group by分组

此时null值会被单独作为一个列进行分组

具体例子：

select name,count(*) from table_null group by name;

3.5 null值参与distinct计算

此时null会参与计算，会进行去重，过滤后会有一个null值

 举例：

select distinct (name) from table_null;

输出结果为：

可以看到null值也参与去重了

3.6 null值参与排序运算

     排序时null也会参与运算，在myql中升序排序时，null值被排在最前面，降序排序时null时会被排在最后。

select name from table_null order by name desc;

3.7 null与功能函数配合使用

例如concat()函数等

select id,name,concat(name,'_',id) from  table_null

结果如下：

3.8 null在建表语句中作为非空约束

3.9 null值的转换

如果null参与运算，一般需要对null进行转换，可以通过以下函数实现：
hive中的nvl()函数
hive中的coalease()函数
mysql中的ifnull()函数等

举例：

select name,coalesce(name,'null') as name1 from table_null;
select name,nvl(name,'null') from table_null;

输出结果：

3.10 null值的底层存储

       null值本身是占用存储空间的，hive中以'/N' 进行存储。以mysql数据库为例验证null值大小 

select name,length(name) from table_null;

       mysql中的null是占用空间的，在创建表的时候尽量把字段的默认值设置成not null，除非是想存储null值。因为在mysql中为null的字段不会走索引，做统计的时候也会直接被忽略掉，如果想统计进去，借助函数进行清洗转换，例如：nvl()函数、 coalease()函数、ifnull()函数等。 null值其实是有东西的，但不显示，只是给个标志，代表无意义的值等。空值''是不占用空间的，''表示空值里面没有值。

3.11 null与空值''区别

1)null在聚合函数(sum,avg,min,max,avg)中会被直接过滤掉，而''不会被过滤掉
2)对于null的判断需要is null或is not null, 而''则需要= 或者 ！=, <>
3)null占用存储空间，''不占用存储空间

4 踩坑案例

  t1表和t2表的字段如图

 

 计算以下sql的输出结果？

with t1 as (
  select 1 as id union all
  select 2 as id union all
  select 3 as id
),
    t2 as (
        select 1 as id union all
        select 2 as id union all
        select 2 as id
    )
 
select  t1.id,t2.id
from t1
left join t2 on t1.id = t2.id
从

输出结果为：

解析： where后面跟着的是对右表的限制条件  where t2.id <>2;

          结论：在最后临时表的基础上进行筛选，返回符合where过滤条件的行；

          注意： sql中比较的结果一般有：true, false, null； 而where条件只会过滤出true的结果。再一次验证了以下结论：

对null进行判断时，只能用is null或is not null，不能采用 =, <, <>, != 等这些操作符

5 小结

    该篇文章主要对null值使用的方法和细节进行了归纳总结。