最近发现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 版权协议,转载请附上原文出处链接和本声明。