SQL中的null

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的成绩是空的（没参加考试），然后执行如下SQL。

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

结果返回：这学期教导主任的302班学生数学平均成绩是57.25分，四舍五入为58分，不及格。
执行结果也出来了，也不报错，从数据看，平均分应该不会低于60。经过排查发现，原来做预处理的时候把没参加考试的学生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值视为相等，较好理解，不做数据测试。