Query difference between 2 tables

[Brenden Cobb <Br...@humedica.com>]

Hi-

I'm stuck on Hive .10 right now and I'm trying to figure out how to accomplish the equivalent of a not exists or minus statement:

Select x from t1 where x not in ( select x from t2)

I know this kind of subquery is available in .13 but would like to find a workaround.

Appreciate any suggestions.

Re: Query difference between 2 tables

[Brenden Cobb <Br...@humedica.com>]

Perfect, thanks very much.

-BC

From: Gunther Hagleitner <gh...@hortonworks.com>>
Reply-To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Date: Tuesday, July 22, 2014 3:03 PM
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Subject: Re: Query difference between 2 tables

select x from t1 left outer join t2 on t1.x = t2.x where t2.x is null

should work.

Thanks,
Gunther.


On Tue, Jul 22, 2014 at 11:01 AM, Brenden Cobb <Br...@humedica.com>> wrote:
Hi-

I'm stuck on Hive .10 right now and I'm trying to figure out how to accomplish the equivalent of a not exists or minus statement:

Select x from t1 where x not in ( select x from t2)

I know this kind of subquery is available in .13 but would like to find a workaround.

Appreciate any suggestions.


CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to which it is addressed and may contain information that is confidential, privileged and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, you are hereby notified that any printing, copying, dissemination, distribution, disclosure or forwarding of this communication is strictly prohibited. If you have received this communication in error, please contact the sender immediately and delete it from your system. Thank You.

Re: Query difference between 2 tables

[Gunther Hagleitner <gh...@hortonworks.com>]

select x from t1 left outer join t2 on t1.x = t2.x where t2.x is null

should work.

Thanks,
Gunther.


On Tue, Jul 22, 2014 at 11:01 AM, Brenden Cobb <Br...@humedica.com>
wrote:

>  Hi-
>
>  I'm stuck on Hive .10 right now and I'm trying to figure out how to
> accomplish the equivalent of a not exists or minus statement:
>
>  Select x from t1 where x not in ( select x from t2)
>
>  I know this kind of subquery is available in .13 but would like to find
> a workaround.
>
>  Appreciate any suggestions.
>

-- 
CONFIDENTIALITY NOTICE
NOTICE: This message is intended for the use of the individual or entity to 
which it is addressed and may contain information that is confidential, 
privileged and exempt from disclosure under applicable law. If the reader 
of this message is not the intended recipient, you are hereby notified that 
any printing, copying, dissemination, distribution, disclosure or 
forwarding of this communication is strictly prohibited. If you have 
received this communication in error, please contact the sender immediately 
and delete it from your system. Thank You.