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

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) <[EMAIL PROTECTED]> 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
>

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 <[EMAIL PROTECTED]> 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) <[EMAIL PROTECTED]> 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
>>
>

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:[EMAIL PROTECTED]]
Sent: Wednesday, November 03, 2010 1:14 PM
To: [EMAIL PROTECTED]
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