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

最近发现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  |
+-------+--------------+