HIVE SQL Subquery in WHERE Clause

问题:

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.

回答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)

回答2:

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

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

回答3:

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