hive中的in /exists, not in / not exists

 

in的改写

考虑以下 SQL 查询语句：

SELECT a.key, a.value FROM a
WHERE a.key in (SELECT b.key FROM B);

可以改为：

SELECT a.key, a.value
FROM a LEFT OUTER JOIN b ON (a.key = b.key)
WHERE b.key <> NULL;

一个更高效的实现是利用 left semi join 改写为：
SELECT a.key, a.val
FROM a LEFT SEMI JOIN b on (a.key = b.key);

 

not in 的改写

可以改用 not exists：

eg1.

select * from A  

where not exists 

(select * from B 

where A.uid=B.uid and A.goods=B.goods); 

 

select dw.apply_id 
from d_extra.dw_order_dkw dw
where dw.topicdate = '2017-08-01' 
and not exists(
select rpt.apply_id
from report.report_dkw_apply_detail rpt
where rpt.topicdate = '2017-08-01' 
and dw.apply_id = rpt.apply_id
)

 

或者用join，然后选择没连接上的：

select t1.a, t2.b
from table1 t1
left join table2 t2 on (t1.a = t2.a and t1.b = t2.b)
where t2.a is null

 

update：

据说Hive对子查询的支持很有限。它只允许子查询出现在SELECT语句的FROM子句中。
如果发现Hive不支持你写的子查询，可以看看能不能把它写成连接操作。例如，一个IN子查询可以写成一个半连接或连接。

查hive官网：hive在0.13版本以后开始支持更多的子查询，如in ，not in的子查询。如果我们用的hive不支持如in，exists,not in等子查询，很可能是0.13版本之前的旧版本。

 

此外，需要注意not in 和 not exists 不完全相同的：

t1
1 2
1 3

t2
1 2
1 null

select * from #t1 where c2 not in(select c2 from #t2);　　-->执行结果：无
select * from #t1 where not exists(select 1 from #t2 where #t2.c2=#t1.c2)　　-->执行结果：1　　3

 

ref:
https://blog.csdn.net/qq_26442553/article/details/80888575
https://blog.csdn.net/zhangge360/article/details/84865567
https://www.cnblogs.com/liyasong/p/sql_in_exists.html
https://www.cnblogs.com/seasons1987/archive/2013/07/03/3169356.html