Hive QL Except clause
I don't think there's any built-in way to do this but a LEFT OUTER JOIN
should do the trick.
This selects all Ids from table1
that do not exist in table2
:
SELECT t1.id FROM table1 t1 LEFT OUTER JOIN table2 t2 ON (t1.id=t2.id) WHERE t2.id IS NULL;
We can use NOT EXISTS clause in Hive as MINUS equivalent.
SELECT t1.id FROM t1 WHERE NOT EXISTS (SELECT 1 from t2 WHERE t2.id = t1.id);