hadoop - Hive doesn't support in, exists. How do I write the following query?

Question

I have two tables A and B that both have a column id. I wish to obtain ids from A that are not present in B. The obvious way is:

SELECT id FROM A WHERE id NOT IN (SELECT id FROM B)

Unfortunately, Hive doesn't support in, exists or subqueries. Is there a way to achieve the above using joins?

I thought of the following

SELECT A.id FROM A,B WHERE A.id<>B.id

But it seems like this will return the entirety of A, since there always exists an id in B that is not equal to any id in A.

Answer 1

You can do the same with a LEFT OUTER JOIN in Hive:

SELECT A.id
FROM A
LEFT OUTER JOIN B
ON (B.id = A.id)
WHERE B.id IS null