Hive中join条件过滤on和where的区别

1、inner join

• inner join首先可以通过分区进行过滤，防止全表扫描。
• inner join会自动为join的键（on d.id=u.department_id）添加is not null的过滤条件
• inner join 下on和where后面的条件进行过滤，在inner join中where和on是同时进行过滤，没有顺序的区别

2、left/right join

• left/right join使用分区条件过滤，可以防止全表扫描，最优先过滤
• left/right join在主表下进行过滤，使用on和where过滤的结果是不一样的，当使用where对主表进行过滤的时候，先过滤再进行left join。当使用on对主表进行过滤，先在非主表进行过滤标记，然后再对全表进行left join时根据过滤条件把不符合条件的行中来源于非主表的数据设置为null。
• left/right join在非主表下进行过滤，使用on和where过滤的结果是不一样的，如果是在on下面添加条件过滤，则先进行表的数据过滤，然后在进行left join，如果是在where后面添加条件过滤，则是先进行left join，然后在对left join得到的结果进行where条件的过滤，所以过滤非主表的时候可以通过on进行条件过滤，这样防止写子查询
• left/right join不会对join键自动添加is not null的过滤条件，所以在left/right join的时候要注意join键为null的情况，这里是可以做优化的

3、full join

• full join中on下条件下过滤(有疑问，还待探究)
• full join中where下条件过滤，不管是不是分区过滤，都是先进行full join，在根据条件进行过滤，这里推荐子查询先过滤在进行full join
• 不会对join键自动添加is not null的过滤条件

转自：https://www.cnblogs.com/zsql/p/14183904.html#_label2

作者：一寸HUI
出处：https://www.cnblogs.com/zsql/