Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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
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
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB