How to Replace IN and NOT IN in Hive

In Hive, you cannot always use IN and NOT IN the same way you would in PostgreSQL or MySQL. Similar logic is usually implemented with joins instead.

Implementing IN

To replace an IN clause, you can use LEFT SEMI JOIN.

Original query:

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

Hive version:

SELECT a.key, a.val
FROM a
LEFT SEMI JOIN b ON (a.key = b.key)

Implementing NOT IN

Original query:

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

In Hive, this can be rewritten with a LEFT OUTER JOIN plus a NULL check:

SELECT t1.key
FROM
(SELECT key FROM a) t1
LEFT OUTER JOIN
(SELECT key FROM b) t2
ON (t1.key = t2.key)
WHERE t2.key IS NULL