Hive中Inner join、Outer join、Full join中on与where的执行计划与结果区别

背景

本文主要讨论hive(版本2.3)中，不同join方式下on条件和where条件的区别，同时关注hive中如何执行语句。比如谓词下推，就是其中一种优化技术。

原表

person表

person.id       person.name     person.age
3       mili    21
4       tom     19
5       mike    18
8       nul     20

account表

account.id      account.account
1       2222
2       2323
3       1111
4       1212
5       5555
6       6666

Inner join

通常情况下简写为join
inner join，查询条件在on中和where中，执行结果上并没有不同。
比如，下面几个查询语句执行结果相同

select * from person join account on person.id=account.id and person.id=4;
select * from person join account on person.id=account.id and account.id=4;
select * from person join account on person.id=account.id where person.id=4;
select * from person join account on person.id=account.id where account.id=4;
select * from person join account on person.id=account.id and person.id=4 and account.id=4;
select * from person join account on person.id=account.id where person.id=4 and account.id=4;

执行结果：

person.id       person.name     person.age      account.id      account.account

4       tom     19      4       1212

通过explain查看上述执行语句转换的mapreduce任务，所有的任务都被转化为相同的执行计划：

hive (test)> explain select * from person join account on person.id=account.id and person.id=4 and account.id=4;
O