hive anti join 的几种写法

t_a 表的记录如下

c1 |
:———— |
a |
b |
c |

生成 SQL 如下：

create table t_a(c1 string);
insert into t_a values("a"),("b"),("c");

t_b 表的记录如下

c1
b
m

生成 SQL 如下：

create table t_b(c1 string);
insert into t_b values("b"),("m");

我们要在t_a 中出现，而不在 t_b中出现的记录。
结果需要为：

c1
a
c

• 写法1 — 使用not in

select * from t_a 
where c1 not in(select c1 from t_b);

• 写法2 —使用 left join 关联上的去掉
  这种写法不容易读懂。

select a.* from t_a a left join t_b b
on a.c1=b.c1
where b.c1 is null;

注意 left join 中 b.c1 is null 不能谓词下推。

生成的执行计划如下。注意，在 join 后才过滤 _col1 is null，关联上的 _col1 肯定是 not null，所以关联上的全去掉。

Plan optimized by CBO.Vertex dependency in root stage
Map 1 <- Map 2 (BROADCAST_EDGE)Stage-0Fetch Operatorlimit:-1Stage-1Map 1 vectorizedFile Output Operator [FS_20]Select Operator [SEL_19] (rows=1 width=93)Output:["_col0"]Filter Operator [FIL_18] (rows=1 width=93)predicate:_col1 is nullMap Join Operator [MAPJOIN_17] (rows=2 width=93)Conds:SEL_16._col0=RS_15._col0(Left Outer),Output:["_col0","_col1"]<-Map 2 [BROADCAST_EDGE] vectorizedBROADCAST [RS_15]PartitionCols:_col0Select Operator [SEL_14] (rows=2 width=85)Output:["_col0"]TableScan [TS_2] (rows=2 width=85)ods@t_b,b,Tbl:COMPLETE,Col:NONE,Output:["c1"]<-Select Operator [SEL_16] (rows=2 width=85)Output:["_col0"]TableScan [TS_0] (rows=2 width=85)ods@t_a,a,Tbl:COMPLETE,Col:NONE,Output:["c1"]Time taken: 0.159 seconds, Fetched: 29 row(s)

• 写法3 — except
  这种写法运行速度比较慢，并且如果每个表有多个字段，但是，仅按少数的字段进行判断的话就不适合。

select * from t_a except select * from t_b;