Hive中in包含子查询

目前 HIVE 不支持 not in 中包含查询子句的语法，因此需要使用 left semi join 或者 left outer join 来实现这一功能。

SQL:

SELECT a.key, a.value FROM a WHERE a.key IN (SELECT b.key FROM B)
SELECT a.key, a.value FROM a WHERE a.key NOT IN (SELECT b.key FROM B)

转换为 HiveQL 的写法:

SELECT a.key, a.value FROM a LEFT SEMI JOIN b ON (a.key = b.key)
SELECT a.key, a.value FROM a LEFT OUTER JOIN b ON (a.key = b.key) WHERE b.key1 IS NULL