You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by "איל (Eyal)" <ey...@gmail.com> on 2010/11/03 13:13:58 UTC

NOT IN query

Hi,

I have a table A with some values and another table B with some other values

How do I get all the distinct values from A that are NOT in B

e.g

if table A has values 1,2,3,4,1,2,3,5,6,7  and B has values 2,3,4,5,6

then the result should be 1,7

Thanks
  Eyal

RE: NOT IN query

Posted by Bennie Schut <bs...@ebuddy.com>.
You can use a left outer join which works in all databases.

select a.value
from tablea a
   left outer join tableb b on (b.value = a.value)
where b.value is null;

Databases are generally pretty good at doing joins so this usually performs good.

________________________________
From: איל (Eyal) [mailto:eyalb1@gmail.com]
Sent: Wednesday, November 03, 2010 1:14 PM
To: hive-user@hadoop.apache.org
Subject: NOT IN query

Hi,

I have a table A with some values and another table B with some other values

How do I get all the distinct values from A that are NOT in B

e.g

if table A has values 1,2,3,4,1,2,3,5,6,7  and B has values 2,3,4,5,6

then the result should be 1,7

Thanks
  Eyal

Re: NOT IN query

Posted by Tim Robertson <ti...@gmail.com>.
Please try this in Hive:
select distinct a.id from tableA a LEFT OUTER join tableB b on
a.id=b.id where b.id is null

Cheers,
Tim


On Wed, Nov 3, 2010 at 1:19 PM, Tim Robertson <ti...@gmail.com> wrote:
> In SQL you use a left join:
>
> # so in mysql:
> select distinct a.id from tableA a left join tableB b on a.id=b.id
> where b.id is null
>
> Not sure exactly how that ports to Hive, but it should be something
> along those lines.
>
> HTH,
> Tim
>
>
> On Wed, Nov 3, 2010 at 1:13 PM, איל (Eyal) <ey...@gmail.com> wrote:
>> Hi,
>>
>> I have a table A with some values and another table B with some other values
>>
>> How do I get all the distinct values from A that are NOT in B
>>
>> e.g
>>
>> if table A has values 1,2,3,4,1,2,3,5,6,7  and B has values 2,3,4,5,6
>>
>> then the result should be 1,7
>>
>> Thanks
>>   Eyal
>>
>

Re: NOT IN query

Posted by Tim Robertson <ti...@gmail.com>.
In SQL you use a left join:

# so in mysql:
select distinct a.id from tableA a left join tableB b on a.id=b.id
where b.id is null

Not sure exactly how that ports to Hive, but it should be something
along those lines.

HTH,
Tim


On Wed, Nov 3, 2010 at 1:13 PM, איל (Eyal) <ey...@gmail.com> wrote:
> Hi,
>
> I have a table A with some values and another table B with some other values
>
> How do I get all the distinct values from A that are NOT in B
>
> e.g
>
> if table A has values 1,2,3,4,1,2,3,5,6,7  and B has values 2,3,4,5,6
>
> then the result should be 1,7
>
> Thanks
>   Eyal
>