hive在on过滤和在where过滤的对比

转载文章地址 https://baijiahao.baidu.com/s?id=1709844516138240964&wfr=spider&for=pc

总结
left join A表的过滤条件在ON 后面的会失效

left join B表 where后面的过滤条件会转为内连接

结论

脚本

oracle 脚本

create table t1(id number(10),name varchar2(30),status varchar2(2));
create table t2(id number(10),mobile varchar2(30));
insert into t1 values(1,'a','1');
insert into t1 values(2,'b','1');
insert into t1 values(3,'c','1');
insert into t1 values(4,'d','1');
insert into t1 values(5,'e','1');
insert into t1 values(6,'f','0');
insert into t1 values(7,'g','0');
insert into t1 values(8,'h','0');
insert into t1 values(9,'i','0');
insert into t1 values(10,'j','0');
insert into t2 values(1,'12345');
insert into t2 values(2,'23456');
insert into t2 values(3,'34567');
insert into t2 values(6,'67890');
insert into t2 values(7,'78901');

SELECT * FROM t1;
SELECT * FROM t2;

1.Inner join

SELECT * 
  FROM T1 
 INNER JOIN t2 
    ON t1.id=t2.id 
   AND t1.status='1' 

   
   
   select * 
     from t1 
    inner join t2 
       on t1.id=t2.id 
    where t1.status='1';

2.left join

   select * 
     from t1 
     left join t2 
       on t1.id=t2.id 
      and t1.status='1' 
      and t2.id<3;
     
  
     
   select * 
     from t1 
     left join t2 
       on t1.id=t2.id 
      and t1.status='1' 
    where t2.id<3;     
     
     
     
     
     select * 
       from t1 
       left join t2 
         on t1.id=t2.id 
      where t1.status='1' 
        and t2.id<3;

谓词放在where后面，先过滤，再连接

注意连接方式变成了hash join，这是因为右表的谓词过滤条件写在where后面，数据库内核优化器CBO会把左连接等价为内连接。cost based optimization

(3)右表的谓词写在on后面，左表的谓词写在where后面：

select * 
  from t1 
  left join t2 
    on t1.id=t2.id 
   and t2.id<3
 where t1.status='1'; 

当把对右表的过滤写在on后面，先对两表进行过滤，再进行left join，显示结果集与写在where后面是不同的，连接方式还是左外连接，显示t1过滤后的全部数据。

– 右表的谓词写在where后面，左表的谓词写在on后面：

select * 
  from t1 
  left join t2 
    on t1.id=t2.id 
   and t1.status='1' 
 where t2.id<7;



explain plan FOR select * 
  from t1 
  left join t2 
    on t1.id=t2.id 
   and t1.status='1'
 where t1.status='0';
 ORDER BY 1;

从执行计划看出，虽然t2表返回0行，步骤3上的filter条件肯定不成立，但有逻辑读消耗，
所以推断它依然进行了全表扫描，所以这种语句对t2表的扫描是对资源的一种浪费，没有意义。
或许你会觉得谁会这么无聊写这种SQL，但是在开发过程中，SQL语句经常是各种过滤条件组合经过拼接而成，
因为返回结果是对的，他们意识不到会出现这种问题，在此说明此种情况主要是想说明一件事：不要总想着用一个语句来解决所有的功能需求，
适当的拆分对性能的提升是很有必要的。

```sql
 select * 
  from t1 
  left join t2 
    on t1.id=t2.id 
   and t1.status='1' -- 条件不成立
 where t1.status='0'
 ORDER BY 1;


SELECT * 
  FROM t1
  FULL JOIN t2
    ON t1.id=t2.id 
   AND t2.mobile='12345'
   AND t1.status='1';