SQL使用技巧(5)NULL值和空值的重要说明

重要结论（工具有特殊修改的除外，例如星环TDH）：
1）NULL值不参与谓词运算，即=、<>均无效，判断必须是is null或者is not null；（3.1节 1+1<2）
2）NULL值做length()计算，结果还是null；（3.2长度计算）
3）NULL值和任意内容concat()或者||，结果都是NULL；（3.3字段拼接）
4）NULL表示一个不存在的对象或者值，是一种逻辑空，而不是物理空。在计算机中，物理空指没有被任何数据填充的内存空间，而null只是表示一个没有值的状态，不占用内存空间。因此，可以说null是一种数据类型和一个特殊的值，它只是占用一个指针或变量的内存部位，而不是真正的物理空间。（2.2生成数据）

一.NULL值判断小工具

对于不同的数据库，NULL值的用法也大同小异，可以使用下面的语句先判断当前库对NULL值操作的支持情况。其中需要注意的是，例如Orcale这样的库需要填默认表dual才能运行。
其中包括了，null值做谓词计算（c1-c6）、null值的concat使用（c7-c9）、null和’'的长度判断（c10-c11），具体功能见下文详细说明。

select case when cl1 is null then null else 255 end   -- null<>1
	,case when cl2 is null then null else 255 end	-- null=1
	,case when cl3=1 then 1 else 255 end			-- is null
	,case when cl4=0 then 0 else 255 end			-- is not null
	,case when cl5 is null then null else 255 end	-- null=null
	,case when cl6 is null then null else 255 end	-- null<>null
	,case when cl7 is null then null else 255 end	-- null||...
	,case when cl8 is null then null else 255 end	-- null||''||...
	,case when cl9 is null then null else 255 end	-- null||' '||...
	,case when cl10='' then '' else 255 end			-- trim(' ')
	,case when cl11 is null then null else 255 end		-- length(NULL||'')
from 
(select 
	null<>1 as cl1
	,null=1 as cl2
	,null is null as cl3
	,null is not null cl4
	,null=null cl5
	,null<>null cl6
	,null||'123' cl7
	,null||''||null cl8
	,null||' '||null cl9
	,trim(' ') cl10
	,length(NULL||'') cl11
-- from dual;	-- 如果不能执行可能是需要添加默认表,dual是orcale的默认表
-- from db.table limit 1;  -- 如果不知道哪个是默认表，也可以用自己的表代替
	) a;

二.Hive中创建简单数据

2.1建表并验证建表

create table null_demo(
  id bigint, 
  name string, 
  age int, 
  city string, 
  address string, 
  notes string)
row format DELIMITED
    fields terminated by ',' 
;

hadoop fs -ls /user/hive/warehouse

2.2生成数据

将本地名称为null_demo.txt的文件，put到hadoop，存为HDFS。文件内容如下

1,张三,30,广东省,中山市,
2,李四,20,,佛山市,备注#能源开采#专科#已婚#有车有房
3,和尚,10,广东省,东莞市,备注#小学生#小学生#有对象#豪宅豪车
4,政委,60,,东莞市

执行 hadoop fs -put /home/null_demo.txt /user/hive/warehouse/null_demo 上传文件
执行 hadoop fs -ls /user/hive/warehouse/null_demo 查看文件夹下内容，null_demo表查询的内容就在这里

查询表内数据如下：

2.3简单的逻辑演示

select * from null_demo where notes is null;

/* 返回结果
id|name|age|city|address|notes|
--|----|---|----|-------|-----|
 4|政委  | 60|东莞市 |       |     |
 */

select * from null_demo where notes is not null;

/* 返回结果
id|name|age|city|address|notes              |
--|----|---|----|-------|-------------------|
 1|张三  | 30|广东省 |中山市    |                   |
 2|李四  | 20|    |佛山市    |备注#能源开采#专科#已婚#有车有房 |
 3|和尚  | 10|广东省 |东莞市    |备注#小学生#小学生#有对象#豪宅豪车|
 */

select * from null_demo where notes<>null;

/* 返回结果
id|name|age|city|address|notes|
--|----|---|----|-------|-----|
 */

select * from null_demo where notes=null;

/* 返回结果
id|name|age|city|address|notes|
--|----|---|----|-------|-----|
 */

三.重要结论的事实验证

3.1 1+1<2

由下图=和<>的结果可以看出id=4的数据始终没有取到，说明在实际使用中 (‘=’的逻辑) + ( ‘<>’的逻辑) 并不是全部，还有null值的逻辑未被包含，另外该例可以证明，''这种空是参与谓词运算的。

select * from null_demo where notes='备注#能源开采#专科#已婚#有车有房';

select * from null_demo where notes<>'备注#能源开采#专科#已婚#有车有房';

3.2长度计算

对应工具中的C11，length()一个null值结果还是null，并不会像C10一样返回0，id=1和C10一样。

select * from null_demo where LENGTH(notes)>=0;

3.3字段拼接

对应工具中的C7-C9，null值拼接任何值结果都为null。也有例外，例如星环TDH就实现了'1'||null返回1，所以理论是理论结果还是实践为准。

select id,id||notes,id||address from null_demo; 

3.4空值的筛选

一般对于空值的筛选都采用 is null 和 length(trim())=0 两种判断方式共用，其中trim()用来消除空格，这样既可找到逻辑空，又可找到物理空。trim()=''可替换length(trim())=0。

select * from null_demo where notes is null or length(trim(notes))=0;

3.5聚合计算

NULL不参与count()计数，sum()计算中也不能说就是0

select count(id),count(city),count(notes) from null_demo;

select address,sum(age) from null_demo group by address;

3.6开窗函数

-- 插入一条数据,只有id和name,其他信息缺失
insert into null_demo(id,name) select 5,'公子';

下方事实证明NULL值会参与开窗函数的计算，使用中应该留意NULL值对结果的影响

select *,ROW_NUMBER() over(order by age desc) from null_demo;

select *,ROW_NUMBER() over(order by age) from null_demo;

纸上得来终觉浅，绝知此事要躬行。SQL之路只有一个标准答案——实践成真。

---

声明：本文所载信息不保证准确性和完整性。文中所述内容和意见仅供参考，不构成实际商业建议，可收藏可转发但请勿转载，如有雷同纯属巧合