SQL query subquery in WHERE environment

Question

I have two tables with similar fields, but the query fails when I execute a subquery SELECT

in a sentence WHERE

.

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

I removed error.log

from AWS but the error was something like HIVE didn't recognize SELECT

.

How can I change the structure of the request?

Thank.

+3

user2715877 Sep 30 14 at 15:25

3 answers

Hives have problems with subquery in WHERE clause use JOIN

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

0

Mihai Sep 30 '14 at 15:35

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

0

Prasanna 21 Feb 17 at 14:38

Myles baker · Accepted Answer · 2014-09-30T15:30:31+0000

In the subqueries of the WHERE clause of the HIVE Language Guide :

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