1、情景描述

在实际数仓开发中，我们经常会遇到多表关联，这个时候就会涉及到where与on的使用

Hive中的where与on在HQL中的区别为：

• 相同点
  • where与on都是用作筛选条件
• 不同点
  • on会显示所有匹配条件的值，不匹配条件的数据补NULL；where只显示满足条件的数据
  • on作用不同类型的多表连接时结果不同；where只起连接作用，不同类型的多表连接时结果相同

首先，我们来看一个例子：使用a表最新分区数据关联b表：

写法一：

select * from a join b on a.id=b.id where a.dt='20231101'

写法二：

select * from a join b on a.id=b.id and a.dt='20231101'

写法三：

select * from (select * from a where dt='20231101') t join b on t.id=b.id

三种写法的结果是等同的，也没有问题。如果需要以a表作为主表，关联查询b表，只需要修改连接类型为左外连接就可以了

但三种写法存在效率上的差异！

写法一与写法三都为高效写法，Hive只会取指定分区的数据进行join

写法二则效率较低，Hive先会查出所有数据进行join，然后再去过滤指定分区的数据

这可以通过explain执行计划证明：

图片后面补充

从执行计划可以得出，写法一的分区数据在一开始扫描时候就已经过滤了。而写法二会拿所有的数据进行查询join，最后再进行过滤

这种将过滤表达式提前执行的过程我们称为谓词下推

2、Hive谓词下推

2.1、什么是谓词下推

谓词下推（Predicate Pushdown，PPD）是指将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。简而言之，就是在合适的场景下，优先执行过滤条件

2.2、Hive谓词下推

在Hive生成物理执行计划中，有一个配置项用于管理谓词下推优化是否开启：

set hive.optimize.ppd=true;

需要注意的是，Hive的PPD控制参数默认开启

3、谓词下推规则

Hive官网谓词下推介绍：https://cwiki.apache.org/confluence/display/Hive/OuterJoinBehavior

基本概念：

• 保留行表(Preserved Row table)： 外连接中的表必须返回所有行。对于左外连接，左表是保留行表；对于右外连接，右表是保留行表；对于全外连接，两个表都是保留行表
• 空供应表(Null Supplying table)： 该表的不匹配行中的列使用空值填充。对于左外连接，左表数据全部返回，左表在右表中无法匹配的数据的列用NULL表示；对于右外连接，刚好相反；对于全外连接，左表和右表都会用NULL来填充无法匹配的数据
• JOIN中的谓词(During Join predicate)： join on子句中的谓词。例如，在R1 join R2 on R1.x = 5中，谓词R1.x = 5是JOIN中的谓词
• JOIN后的谓词(After Join predicate)： where子句中的谓词。例如，在R1 join R2 on R1.m = R2.n where R1.x = 5中，谓词R1.x = 5是JOIN后的谓词

官网对谓词下推规则的概括：

• 在JOIN期间谓词不能被推过保留行表
• JOIN后的谓词不能被推过空供应表

总结就是：

• 保留行表的谓词写在join中不能下推，需要用where
• 空供应表的谓词写在join后不能下推，需要用on
• 在join关联情况下，过滤条件无论在join中还是where中谓词下推都生效
• 在full join关联情况下，过滤条件无论在join中还是where中谓词下推都不生效

因此，可以得到如下谓词下推规则表：

item[inner] joinleft [outer] joinright [outer] joinfull [outer] joinleft tableright tableleft tableright tableleft tableright tableleft tableright tablewherePPDPPDPPDNot PPDNot PPDPPDNot PPDPPDonPPDPPDNot PPDPPDPPDNot PPDNot PPDNot PPD

4、谓词下推场景分析

Hive谓词下推规则表中各场景分析案例详见文章：传送门