在处理流水增量表的时候,出现了一个判定的失误。
select a.a1,a.a2
from
(
select
a.a1
,if(a.a2<>b.b2,1,0) as diff
,a.a2
from a
lefter join b
on a.a1=b.b1
) c
where c.diff=1;
因为左外关联,可能会出现b表数据不存在 则b.b2 is null , if(a.a2<>b.b2,1,0) as diff,null值的判断只能使用is ,is not
0: jdbc:hive2://localhost:10000/big12> select if(1 <>null,0,1);
+------+--+
| _c0 |
+------+--+
| 1 |
+------+--+
1 row selected (0.13 seconds)
0: jdbc:hive2://localhost:10000/big12> select 1<>null;
+-------+--+
| _c0 |
+-------+--+
| NULL |
+-------+--+
1 row selected (0.127 seconds)