HIVE SQL Subquery in WHERE Clause

Question

I have two tables with similar fields but the query is failing when I am doing a SELECT subquery in a WHERE clause.

SELECT foo 
FROM   bar
WHERE  fizz IN (SELECT fizz FROM fuzz)

I deleted the error.log from AWS but the error was something to the extent that HIVE did not recognize the SELECT.

How do I need to restructure this query?

Thanks.

Answer 1

From the Subqueries in the WHERE Clause section of the HIVE Language Manual:

SELECT b.foo FROM bar b WHERE b.fizz IN (SELECT f.fizz FROM fuzz f)

Answer 2

Hive does not support IN, EXISTS or subqueries in the WHERE clause. Go for a cross join...

Answer 3

Hive has problems with subquery in the WHERE clause use a JOIN

SELECT foo FROM bar 
JOIN fuzz
ON bar.fizz=fuzz.fizz