注意！SQL中的NULL

大家好，我是宝器！

越发觉得取数之前的“预处理”非常重要，其中最核心的一点是检查数据的准确性。大的方向有两种，其一，确认数据本身无错乱，其二，保障取数业务逻辑准确。

第一种比较繁琐、耗时，但是好处理（习惯对结果做一下统计值分布可以减少很多异常）。第二种不是SQL执行过程中报错，而是返回的结果和你需要的不太一样。今天主要聊一下取数分析中容易忽略的点，尤其是SQL中的NULL值。

1、空值JOIN时导致数据丢失

创建案例数据表

CREATE TABLE IF NOT EXISTS tmp_test_3
(
id_1 INT,
col_1 VARCHAR(255),
col_2 VARCHAR(255)
);


CREATE TABLE IF NOT EXISTS tmp_test_4
(
id_2 INT,
col_3 VARCHAR(255),
col_4 VARCHAR(255)
);


INSERT INTO tmp_test_3(id_1, col_1, col_2) VALUES (1,'a',null);
INSERT INTO tmp_test_3(id_1, col_1, col_2) VALUES (2,'b','join_key_1');
INSERT INTO tmp_test_3(id_1, col_1, col_2) VALUES (3,'c','join_key_2');


INSERT INTO tmp_test_4(id_2, col_3, col_4) VALUES (1,'a',null);
INSERT INTO tmp_test_4(id_2, col_3, col_4) VALUES (2,'b','join_key_1');
INSERT INTO tmp_test_4(id_2, col_3, col_4) VALUES (3,'c','join_key_2');

查看下tmp_test_3、tmp_test_4两个案例表的数据（分布是类似的）

现在有个业务，部分数据存在tmp_test_3表，有一些存在tmp_test_4表，假设要得到两个表中的数据，需要这两个表按col_2、col_4列JOIN连接。

SELECT 
  * 
FROM 
  tmp_test_3 t_a 
Left JOIN 
  tmp_test_4 t_b 
on 
  t_a.col_2 = t_b.col_4 ;

执行一下上面的语句，会得到什么结果。

结果显示是col_2和col_4为空的数据是丢失了的。

为什么？

直接说原因：在tmp_test_3和tmp_test_4表中用于join的列存在NULL值，而NULL和任何值做比较都是返回的NULL（即不能对NULL进行！=、=、>、<等判断，返回是NULL）。

Coalesce真香函数，将空值替换成一个默认值。

SELECT 
  * 
FROM 
  tmp_test_3 t_a 
JOIN 
  tmp_test_4 t_b 
on 
  COALESCE(t_a.col_2 ,'aaa')=  COALESCE(t_b.col_4 ,'aaa')

这样就可以把tmp_test_3中包含NULL的数据记录和tmp_test_4表中的NULL数据记录JOIN起来。但是这里有个小问题是他会把这些NULL记录全部匹配，所以实际应用中可以按照业务需求来做取舍。

2、聚合运算时遇到NULL值

以下是教导主任的302班学生数学成绩表，对应了学生名字和成绩。

CREATE TABLE IF NOT EXISTS tmp_score_baoqi_1
(
col_name VARCHAR(255),
col_core int
);

INSERT INTO tmp_score_baoqi_1(col_name, col_core) VALUES ('a',null);
INSERT INTO tmp_score_baoqi_1(col_name, col_core) VALUES ('b',86);
INSERT INTO tmp_score_baoqi_1(col_name, col_core) VALUES ('c',78);
INSERT INTO tmp_score_baoqi_1(col_name, col_core) VALUES ('d',65);

你验证数据的时候发现有学生a的成绩是空的（没参加考试），心里美滋滋的预处理并且开始执行如下脚本。

SELECT
 avg(IFNULL(col_core, 0 )  ) 
FROM
 tmp_score_baoqi_1
  
-- 返回57.2500

结果返回：这学期教导主任的302班学生数学平均成绩是57.25分，四舍五入为58分，不及格。

很好，执行结果也出来了，也不报错，但是教导主任却生气了，质疑怎么可能他的班上学生数学成绩不及格，需要你核查。

经过排查你发现，原来你做预处理的时候把没参加考试的学生a缺少的数学成绩也算在内，用数值0代替NULL，严重影响了最终成绩。

这个小例子想说明的就是做聚合运算时要注意NULL值，一定要清楚count、sum、avg函数对NULL的处理：

avg：

SELECT
 avg(col_core),avg(IFNULL(col_core, 0 )  ) 
FROM
 tmp_score_baoqi_1
  
-- 返回76.33、57.2500

count：

SELECT
 count(1),count(*),count(col_core)
FROM
 tmp_score_baoqi_1
  
-- 返回4、4、3

sum：可以对单个列求和，也可以对多个列运算后求和忽略NULL值，且当对多个列运算求和时，如果运算的列中任意一列的值为NULL，则忽略这行的记录。

补充一条，DISTINCT、ORDER BY、GROUP BY 遇到NULL值视为相等，较好理解，不做数据测试。

请务必注意细节，大概率能决定成败，本节完。

·················END·················

1、空值JOIN时导致数据丢失

创建案例数据表

2、聚合运算时遇到NULL值

更多相关文章

随机推荐