ORACLE LEFT JOIN时如果被匹配的两个字段都为NULL，被视作不相等

 

select a1,b1,a2,b2,a1.c from

(select 1 as a1,2 as b1 ,null as c )a1

left JOIN

(select 3 as a2,2 as b2 ,null as c )a2

on a1.c= a2.c

 

该查询的结果是：

a    b    a2    b2    c
1    2    \N    \N    \N

 

解决方案是:

select a1,b1,a2,b2,a1.c from

(select 1 as a1,2 as b1 ,(case when c1 is null then "empty" else c1 end ) as c )a1

left JOIN

(select 3 as a2,2 as b2 ,(case when c2 is null then "empty" else c2 end ) as c )a2

on a1.c= a2.c