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-0
  Fetch Operator
    limit:-1
    Stage-1
      Map 1 vectorized
      File 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 null
            Map Join Operator [MAPJOIN_17] (rows=2 width=93)
              Conds:SEL_16._col0=RS_15._col0(Left Outer),Output:["_col0","_col1"]
            <-Map 2 [BROADCAST_EDGE] vectorized
              BROADCAST [RS_15]
                PartitionCols:_col0
                Select 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;