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
>