Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> IN() Operator


Copy link to this message
-
Re: IN() Operator
Andy,

    Are you trying to do something like:

SELECT
FROM mytable  A
WHERE ....
AND mycol IN (
SELECT ....

)

If so, you can't do sub-queries inside the WHERE clause in Hive, you can
only do sub-queries within the FROM/JOIN clause.

But, almost any query similar to above can be written using sub-queries
in the FROM using join instead.

For example:

SELECT ....
FROM mytable A
JOIN ( SELECT .... ) B
ON A.mycol  = B.mycol
WHERE ...

hope that helps.

-- amr

On 2/19/2010 8:58 AM, Andy Kent wrote:
> I couldn't find anything on the wiki so thought I would try here.
>
> Does Hive have an IN() operator similar to in MySQL?
>
> If not then is there an alternative way of testing for inclusion?
>
> Thanks,
> Andy.
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB