Hive谓词下推之FULL JOIN

1、数据准备

准备两张表：

a表数据：

b表数据：

2、需求描述

将a表与b表rank=1的数据进行全连接，最终想要得到这样的结果：

本案例将使用full join连接类型进行演示，并透过full join来反映其它Hive谓词下推通用使用场景，得出普遍结论

3、FULL JOIN谓词下推

1）写法1：

select * from a full join b on a.id=b.id where b.rank=1;
1

结果如下：

解释：

b表先执行where过滤，过滤后再与a表进行full join，因此，b表中rank=2的数据丢失，结果不符合要求

2）写法2：

select * from a full join b on a.id=b.id and b.rank=1;
1

结果如下：

解释：

a表与b表互相没有匹配到的数据显示为NULL，数据不会丢失，但数据冗余，结果不符合要求

3）写法3：

select * from a full join (select * from b where rank=1) t on a.id=t.id;
1

结果如下：

解释：

a表与b表互相没有匹配到的数据显示为NULL，数据不会丢失，也不会冗余，结果符合要求

4、普遍结论

通过full join案例，我们可以得出Hive谓词下推时的以下结论：

• ① on会显示所有匹配条件的值，不匹配条件的数据补NULL；where只显示满足条件的数据
• ② on作用不同类型的多表连接时结果不同；where只起连接作用，不同类型的多表连接时结果相同
• ③ 无论使用on或where，一定要去先考虑一下关键词的执行顺序，否则结果可能与预期完全不同

Hive谓词下推详解见文章：传送门