You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Leo Alekseyev <dn...@gmail.com> on 2010/12/16 03:09:59 UTC

How to implement JOIN ON (a.key <> b.key) or ...NOT IN... semantics

I need to get rows from table A where the key is not present in table
B.  Something like

SELECT a.val1, a.val2, a.key, b.val FROM a LEFT OUTER JOIN b ON
(a.key=b.key) WHERE b.val IS NULL

seems to achieve the desired result, but is there a better way?..

RE: How to implement JOIN ON (a.key <> b.key) or ...NOT IN... semantics

Posted by Steven Wong <sw...@netflix.com>.
This is the way to go.


-----Original Message-----
From: Leo Alekseyev [mailto:dnquark@gmail.com] 
Sent: Wednesday, December 15, 2010 6:10 PM
To: <hi...@hadoop.apache.org>
Subject: How to implement JOIN ON (a.key <> b.key) or ...NOT IN... semantics

I need to get rows from table A where the key is not present in table
B.  Something like

SELECT a.val1, a.val2, a.key, b.val FROM a LEFT OUTER JOIN b ON
(a.key=b.key) WHERE b.val IS NULL

seems to achieve the desired result, but is there a better way?..