hive sql 多字段关联注意

多个关联键 需要注意只要其中一个字段为null 整个记录是关联不上的。

如果需要关联上，要nvl或coalesce处理下。

select 
* 
from (select 1 as id1,null as id2,3 as x) as a 
left join (select 1 as id1,null as id2,4 as y) as b 
    on a.id1=b.id1 and a.id2=b.id2

select 
* 
from (select 1 as id1,null as id2,3 as x) as a 
left join (select 1 as id1,null as id2,4 as y) as b 
	on a.id1=b.id1 and nvl(a.id2,'')=nvl(b.id2,'')