背景
本文主要讨论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