hive中in、not in不支持子查询的改写方法

 Error：SemanticException [Error 10249]: Line 1:146 Unsupported SubQuery Expression 'userid': Correlating expression cannot contain unqualified column references.
 hive查询中，这个异常想必大家经常会遇到，在hive中，in、not in 是不支持子查询的，今天来看看替代的方法

先说说需求场景

我们模拟的需求，从A表查询的时候，需要在结果中过滤掉B表中的userid。
上sql

select DISTINCT userid FROM TABLE_A AS a WHERE a.dt >= '20200209' AND a.userid 
NOT IN  (SELECT DISTINCT userid FROM TABLE_B AS b WHERE b.dt >= '20200209');

这是我们的正常逻辑，使用not in 过滤掉b表中的userid。但是hive目前，in、not in 是不支持子查询的，就会报我们开始提到的那个错误：

Error：SemanticException [Error 10249]: Line 1:146 Unsupported SubQuery Expression 'userid': Correlating expression cannot contain unqualified column references

使用join进行改写

使用jion改写，应该是我们最先想到的一种方式，至于连接的方式，应该根据具体的需求具体分析吧，这里使用left jion示例一下，左联以后，加上b表userid为空的条件，就可以实现我们的需求

select DISTINCT a.userid FROM TABLE_A AS  a left JOIN  TABLE_B AS b
on a.userid=b.userid
WHERE b.userid is NULL;

使用EXISTS进行改写

其实我们还可以使用EXISTS进行改写，先上sql

select DISTINCT a.userid FROM TABLE_A AS a WHERE a.dt >= '20200209' 
AND NOT EXISTS  
(SELECT DISTINCT b.userid FROM TABLE_B AS b WHERE b.dt >= '20200209' AND a.userid = b.userid);

EXISTS的语法有时间给大家讲一下，这里就注意几点。
where后面使用NOT EXISTS 时候，不需要跟着字段

NOT IN写法

where userid NOT IN ()

NOT EXISTS写法

where userid NOT EXISTS ()
使用了 NOT EXISTS 后面就可以跟一个子查询，而过滤条件，文中是根据userid过滤的，所以这个通过userid的条件写到了子查询的where条件里面去了。

子查询的过滤条件
这种写法就相当于jion中的on。想实践的小伙伴可以在3A服务器上部署一套。