join是sql中常用的关键字，同样在hive中，join语句也经常被用到，尤其是一些outer join语句，但在使用这样outer join语句时，不小心就会踩到坑里，使结果与预期的不一样，或者使sql执行不够高效。
这里就详细介绍下hive outer join。
下面先看几个概念：
- 保留表(Preserved Row table)
在outer join中需要返回所有数据的表叫做保留表，也就是说在left outer join中，左表需要返回所有数据，则左表是保留表；right outer join中右表则是保留表；在full outer join中左表和右表都要返回所有数据，则左右表都是保留表。
- Null Supplying table(这个怎么翻译？ 支持null的表？？？？)
在outer join中对于没有匹配到的行需要用null来填充的表称为Null Supplying table。在left outer join中，左表的数据全返回，对于左表在右表中无法匹配的数据的相应列用null表示，则此时右表是Null Supplying table，相应的如果是right outer join的话，左表是Null Supplying table。但是在full outer join中左表和右表都是Null Supplying table，因为左表和右表都会用null来填充无法匹配的数据。
此时你会发现full outer join就是个矛盾体，因为此时的左表和右表即使保留表又是Null Supplying table，这就导致在outer join中谓词下放时有一些问题。
- Join中的谓词
Join中的谓词是指 Join On语句中的谓词。如：’R1 join R2 on R1.x = 5’ the predicate ‘R1.x = 5’是Join中的谓词
- Join之后的谓词
where语句中的谓词称之为Join之后的谓词
最好使用子查询的方式进行join
谓词下放
谓词：谓词是一个属性或是一个表示“持有”或“不持有”的表达式，换句话说，也就是取值为 TRUE、FALSE 或 UNKNOWN 的表达式。谓词用于 WHERE 子句和 HAVING 子句的搜索条件中，还用于 FROM 子句的联接条件以及需要布尔值的其他构造中。
对于outer join中的坑，主要是对谓词下放的规则不熟悉，导致理解的不够全面，造成一些坑。
谓词下放的规则有两个，分别为
- Join中谓词如果是保留表的，则不会下放。
- Join之后的谓词如果是Null Supplying tables的，则不会下放。
下面来看写例子来加深理解。
栗子
创建两个表
create table t1 (id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY “,”;
create table t2 (id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY “,”;
t1里的数据如下：
|
|
t2里的数据如下：
|
|
在执行例子之前，避免map join影响到各个例子的执行计划，先关闭map join，
set hive.auto.convert.join=false;
- case 1，sql如下：
select t1.*,t2.name as name_t2 from t1 left outer join t2 on (t1.id=t2.id) where t1.name='a' and t2.name='aa';
此sql中，保留表为t1，Null Supplying table为t2，join中谓词在此sql中没有，where中的两个谓词都是join之后的谓词。
则根据谓词下放规则，t1.name='a'是保留表的字段，则被下放，而t2.name='aa'是Null Supplying table的，Null Supplying table不支持join之后的谓词下放。
那么此时的sql就等同于
|
|
其结果为1 a aa，可能你的期望结果是
|
|
造成这样的结果是因为hive中outer join谓词下放的规则。
case1 sql中的t2.name='aa'是join之后的谓词，不会被下放到t2中(t2是Null Supplying table)，而是对t1和t2 join的结果进行filter，filter的条件是t2.name='aa'，于是就出现了1 a aa结果，因为其余数据的t2.name的值都为NULL。
现在来解析下case1的执行过程。
首先sql中有两个join之后的谓词，分别为t1.name='a' and t2.name='aa'，其中t1.name是保留表t1的，会谓词下放，在scan t1表时，对t1中的数据进行过滤，t2.name是t2的，t2不是保留表，谓词无法下放，而where又是在join之后执行的(Joins occur BEFORE WHERE CLAUSES)。看下sql的执行计划：
|
|
从sql的执行计划上也可以看出，TableScan t1时使用了predicate: (name = 'a') (type: boolean)对数据进行过滤，(则进入mr的t1数据不是全表数据，而是过滤过的数据)。而where对应的filter operator是在join之后才执行的。
- case 2，sql如下：
select t1.*,t2.name as name_t2 from t1 left outer join t2 on (t1.id=t2.id and t2.name='aa') where t1.name='a';
执行结果为
|
|
其中t2.name是join中谓词，t1.name是join之后谓词，根据规则join中谓词能下放到t2中，join之后的谓词能下放到t1中，则上面sql的意思是将t1中name为a的数据与t2中name为aa的数据进行join，t1和t2都会在table scan时进行数据过滤。sql的执行计划如下：
|
|
从执行计划上可以看出t1和t2的TableScan中都存在Filter Operator操作，对数据进行过滤。
- case 3，sql如下：
select t1.*,t2.name as name_t2 from t1 left outer join t2 on (t1.id=t2.id and t1.name='a' ) where t2.name='aa';
此sql中有两个谓词，一个是join中谓词t1.name='a'和join之后谓词t2.name='aa'。
这里需要明白一点，join中谓词决定了进行join的数据，join之后谓词决定了最终要呈现的数据。也就是说join中谓词t1.name='a'决定了t1表中name为a的数据才去和t2进行join，而join之后谓词t2.name='aa'决定了最终的结果。
执行结果为：1 a aa。
这里的结果随便和case 1的结果一样，但是执行的内部逻辑是不一样的，case3在执行where filter之前返回的数据是t1全表的数据，而case1在执行where filter之前返回的数据是t1中name为a的数据，因为case3中join中谓词t1.name='a'并不能对t1进行下放，这里的语义是对t1进行全表扫描，只拿name为a的数据和t2进行join，id匹配成功则为t2.name的值，匹配不成功和name不为a的数据中t2.name的值都为NULL，join结束之后由where filter过滤t2.name='aa'，因此就出现了上面的结果(因为没有匹配成功的和没有进行匹配的数据都是NULL，被过滤)。
|
|
上面是case3的执行计划，可以看出两个表在TableScan时都是全表扫描，没有进行filter，在join时有个filterfilter predicates，join结束之后又有个Filter Operator对join的结果进行过滤。
- case 4，sql如下:
select t1.*,t2.name as name_t2 from t1 left outer join t2 on (t1.id=t2.id and t1.name='a' and t2.name='aa');
此sql中只有join中谓词，根据规则t1.name=’a’不能下放到t1中，而t2.name=’aa’能够下放到t2中，那就是说扫t1的全表数据，然后拿t1.name为a的数据去和t2中t2.name为aa的数据(t2在table scan时会过滤name为aa的数据)进行join，那么t1中name不为a的数据不和t2进行join，直接将t2.name置为NULL，而t1中name为a却没有和t2进行join成功的数据也为NULL，所以其结果如下：
|
|
这里需要说明下，t1是保留表，join之后要返回其表中所有的数据，因为在join之后没有where对其结果进行过滤，所以此处显示的是t1表的所有数据，上面的case之所以没有返回t1的所有数据是因为他们要么在table scan时进行了过滤要么就是在join之后通过where进行了过滤。
为了验证下t1中只有那么为a的数据与t2进行了join，我对t2中的数据进行了修改，添加一条数据4,aa，其执行结果按照上面的逻辑应该不会发生变化，再次执行case4，看下返回的结果是否一致。其结果如下
|
|
与上面的结果一致。
此sql的执行计划如下：
|
|
这四个case介绍了left outer join的情况，right outer join与left类似，只是保留表和Null Supplying table换下位置，比较特殊的是full outer join，在full outer join中join中谓词和join之后谓词都不会被下放，(这里说不会下放可能不太准备，会下放，但是下放的位置不对，下放前和下放后的位置是一样的，开启log可以查看。)
下放前的与下放后的结果如下：
|
|
因为full outer join中的两个表既是保留表也是Null Supplying table。来看个full outer join的例子，select t1.*,t2.name as name_t2 from t1 full outer join t2 on (t1.id=t2.id and t2.name='aa') where t1.name='a';
其结果为
|
|
其sql的执行计划如下：
|
|
从执行计划中可以看出t1和t2在scan时都没有进行filter过滤，也就是说join中谓词和join之后的谓词都没有进行下放，join中谓词是在Join Operator时通过filter predicates进行过滤join，而join之后谓词是在join之后通过Filter Operator进行过滤的。
如果t1和t2某个表是分区表，此时想只full outer join某个分区的数据，应该怎么过滤更高效呢？
此时应该使用子查询，sql为select t1.*,t2.name as name_t2 from t1 full outer join t2 on (t1.id=t2.id and t2.name='aa')
|
|
总结
这篇文章其实想说的有两点，
- outer join中谓词的下放规则
- join on和where的区别，以及执行顺序
根据上面的四个case总结下outer join中谓词的下放规则
| | 保留表 | Null Supplying table |
|———-|———-|——–|
| join中谓词 | case3和case4不下放 | case2和case4下放 |
| join之后谓词 | case1和case2下放 | case1和case3不下放 |
join on和where的区别是join on决定的是什么数据去进行join操作，符合条件的数据才会进行join，不符合条件的数据直接赋值为NULL，而where是对join之后的结果进行过滤，决定最终展示的内容。
where中的谓词如果没有被下放则在join之后执行。
参考
Hive Outer Join Behavior
Understanding Hive Outer Join Behavior
附加
hive开启debug的方式：
- 启动hive cli时，执行
hive --hiveconf hive.root.logger=DEBUG,console - 修改hive的log4j文件，在${HIVE_HOME}/conf/hive-log4j.properties文件中找到hive.root.logger属性，并将其修改为
hive.root.logger=DEBUG,console
