CODEWITHSUNDEEP
sqlhive
user2715877
user2715877

Reputation: 543

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.

Upvotes: 6

Views: 31476

Answers (3)

Prasanna
Prasanna

Reputation: 1

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

Upvotes: -1

Mihai
Mihai

Reputation: 26784

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

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

Upvotes: 0

Myles Baker
Myles Baker

Reputation: 3760

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)

Upvotes: 9

Related Questions