Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - NOT IN query


Copy link to this message
-
RE: NOT IN query
Bennie Schut 2010-11-04, 08:15
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