Hive Sql优化集(on where过滤)

1.问题：考虑下面三个sql

sql1:
select * 
from (select log.*,a.batch_order_no,a.pm_code,a.bank_code,a.pay_status
    from pay_recommend_metric.recommend_log log 
         left join dw_transform_paycenter.all_order_info a
         on (log.orderno=a.order_no and log.dt='2017-09-17' and a.dt='2017-09-17')
         where a.order_no is not null and log.orderno is not null
) t1 left join pay_recommend.pay_type pt
     on (t1.pm_code=pt.pm_code and t1.bank_code=pt.bank_code)

sql2:
select * 
from (select log.*,a.batch_order_no,a.pm_code,a.bank_code,a.pay_status
    from pay_recommend_metric.recommend_log log 
         left join dw_transform_paycenter.all_order_info a
         on (log.orderno=a.order_no)
         where log.dt='2017-09-17' and a.dt='2017-09-17' and a.order_no is not null and log.orderno is not null
) t1 left join pay_recommend.pay_type pt
     on (t1.pm_code=pt.pm_code and t1.bank_code=pt.bank_code)

select * 
from (select log.*,a.batch_order_no,a.pm_code,a.bank_code,a.pay_status
    from (
            select *
            from pay_recommend_metric.recommend_log
            where dt='2017-09-17' and order_no is not null
       ) log 
       left join (
          select * 
          from dw_transform_paycenter.all_order_info
          where dt='2017-09-17' and order_no is not null
       )a
      on (log.orderno=a.order_no)
) t1 left join pay_recommend.pay_type pt
     on (t1.pm_code=pt.pm_code and t1.bank_code=pt.bank_code)

分析：这三个sql区别就在于log表和a表的过滤条件是在where字句上还是on字句上。
sql1的结果：
a表和log表的所有数据做连接，只是在a表的dt!='2017-09-17'记录上a表上所有字段都为空
sql2的结果：
a表和log表的所有数据先做连接，然后过滤出两个表只在'2017-09-17'分区上的数据
sql3的结果：
a表和log表分别查询出'2017-09-17'分区上数据，两个小数据集做关联，结果和sql2的相同

总结：

对于a join b，无论过滤条件放到on中还是where中，结果相同。
对于a left join b，过滤条件在on中：忽略a表的过滤条件，b表的过滤条件只会导致不满足条件的记录在b表的所有字段上都为空；过滤条件在where中：过滤掉所有不满足条件的记录。
把握核心：先做join，left join时on中条件无过滤记录作用，后做where条件过滤记录

sql3的执行效率最高，推荐使用；sql2的效率较低，因为需要全表关联；sql1有bug，不建议直接在on中使用过滤条件
但是注意不要将where过滤条件放到on中，除非你很了解SQL执行后的结果；另外不要将on连接条件放到where中，hive并不会像mysq那样做连接优化，这样会导致不可控的情况