Simulating 'NOT IN' caluse in Hive

Hello,

While designing data pipeline for saavn.com, I faced the need of SQL's 'NOT IN' clause. But Hive does not support 'NOT IN' clause. I came up with a simple workaround for that using a JOIN.


Lets say I have a table A which contains a list of user ids who bought apples and a table B which contains a list of user ids who bought bananas. Problem is to find a list of users who bought apples but not bananas.

Let's write down the schema of the two tables before we proceed :

CREATE TABLE A (uids string);

CREATE TABLE B (uids string); 

If we were to do this in SQL, we would do :
SELECT uid FROM A WHERE uid NOT IN (SELECT uid FROM B) X ;

But since, 'NOT IN' operator is not available in Hive, here is a simple workaround:

SELECT
FROM
(
  SELECT uid
  FROM A
) X
LEFT OUTER JOIN
(
  SELECT uid , 'flag' as  flag
  FROM B
) Y
ON (A.uid )

WHERE flag is null ;

Its easy to notice that flag will be non-null for those who buys both apple and oranges. Similarly, flag will be null for those who buys apples but not oranges.

Conclusion : We can simulate 'NOT IN' operator in Hive by using a JOIN and a dummy flag.