表信息:
用户User表(userid,name)
黑名单BanUser表(userid,name)
问题:
找出不在黑名单中的用户信息(userid,name)
hive暂时不支持以下查询方式:
select aa.* from user aa where aa.user not in (select a.userid from banuser a);
方法一:使用not exists进行改写
select aa.* from user aa
where not exists (select a.userid from banuser a where a.userid=aa.userid);
方法二:使用left join进行改写
select a.userid,a.name
from user a left join banuser b on a.userid=b.userid
where b.userid is null;