

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

