SQL 在Join 和 Exists查询时对Null 值的处理

  • Post author:
  • Post category:其他


最近发现SQL在处理Join 和 父子查询的时候,会对 null 记录做一些很奇怪的处理。根据大佬朱峰的解释,SQL 在当初设计的时候,不是把 null 做为空值来理解的,而是当作

未知

来处理的。所以在后续计算的时候,如发现计算结果

未知

会对记录进行丢弃处理。



Join 中 null 值的处理

  • Inner Join: 左右两张表中的 null 值都会被忽略
  • Outer Join: Streamed 表数据全部保留,Build 表不保留 null 记录
  • Semi Join: 保留 Streamed 表中可以和Build 表关联上的非 null 记录
  • Anti Join: 保留 Streamed 表中不可以和Build 表关联上的记录和 null 记录



In 和 Exists 中 null 值的处理

  • In: 父表和子表的 null 值都会被忽略
  • Not In: 子表包含 null 时,直接返回空
  • Exists: 父表和子表的 null 值都会被忽略
  • Not Exists: 父表中非空的,在子表中存在的值被忽略



Join 和 Exists 测试



准备测试数据

create temp table t1 (id int,name string) using parquet;
create temp table t2 (id int,name string) using parquet;
insert into t1 values(1, 'wankun1');
insert into t1 values(2, 'wankun2');
insert into t1 values(null, 'wankun-null');
insert into t2 values(1, 'wankun1');
insert into t2 values(3, 'wankun3');
insert into t2 values(null, 'wankun-null');



Join 测试

> select * from t1 join t2 on t1.id = t2.id;
+-----+----------+-----+----------+
| id  |   name   | id  |   name   |
+-----+----------+-----+----------+
| 1   | wankun1  | 1   | wankun1  |
+-----+----------+-----+----------+
1 row selected (3.327 seconds)
> select * from t1 LEFT join t2 on t1.id = t2.id;
+-------+--------------+-------+----------+
|  id   |     name     |  id   |   name   |
+-------+--------------+-------+----------+
| 2     | wankun2      | NULL  | NULL     |
| 1     | wankun1      | 1     | wankun1  |
| NULL  | wankun-null  | NULL  | NULL     |
+-------+--------------+-------+----------+
3 rows selected (1.557 seconds)
> select * from t1 RIGHT join t2 on t1.id = t2.id;
+-------+----------+-------+--------------+
|  id   |   name   |  id   |     name     |
+-------+----------+-------+--------------+
| NULL  | NULL     | 3     | wankun3      |
| 1     | wankun1  | 1     | wankun1      |
| NULL  | NULL     | NULL  | wankun-null  |
+-------+----------+-------+--------------+
3 rows selected (1.304 seconds)
> select * from t1 SEMI join t2 on t1.id = t2.id;
+-----+----------+
| id  |   name   |
+-----+----------+
| 1   | wankun1  |
+-----+----------+
1 row selected (2.183 seconds)
>
> select * from t1 ANTI join t2 on t1.id = t2.id;
+-------+--------------+
|  id   |     name     |
+-------+--------------+
| 2     | wankun2      |
| NULL  | wankun-null  |
+-------+--------------+



In 和 Exists 测试

> select * from t1 where id in (select id from t2);
+-----+----------+
| id  |   name   |
+-----+----------+
| 1   | wankun1  |
+-----+----------+
1 row selected (3.161 seconds)
> select * from t1 where id not in (select id from t2);
+-----+-------+
| id  | name  |
+-----+-------+
+-----+-------+
No rows selected (4.775 seconds)
> select * from t1 where exists (select 1 from t2 where t2.id = t1.id);
+-----+----------+
| id  |   name   |
+-----+----------+
| 1   | wankun1  |
+-----+----------+
1 row selected (5.08 seconds)
> select * from t1 where not exists (select 1 from t2 where t2.id = t1.id);
+-------+--------------+
|  id   |     name     |
+-------+--------------+
| 2     | wankun2      |
| NULL  | wankun-null  |
+-------+--------------+



版权声明:本文为wankunde原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。