Re: Re: how to join like a "In A but not in B" operation?

[sac . khurana]

Try this


SELECT a.key. 
 FROM a LEFT SEMI JOIN b on (a.key <> b.key)


Sent from BlackBerry® on Airtel

-----Original Message-----
From: "Raghunath, Ranjith" <ranjith.raghuna...@usaa.com>
Date: Mon, 12 Mar 2012 05:43:09 
To: 'user@hive.apache.org'<user@hive.apache.org>; 
'sac.khur...@gmail.com'<sac.khur...@gmail.com>
Subject: Re: Re: how to join like a "In A but not in B" operation?

Thinking out loud here and may be worth a shot. Please perform an explain to 
see if the where takes place after the join but this may work,

Select a.* from a left join b on a.key=b.key where b.key is null


Thanks,
Ranjith

From: Richard [mailto:codemon...@163.com]
Sent: Monday, March 12, 2012 12:26 AM
To: user@hive.apache.org <user@hive.apache.org>; sac.khur...@gmail.com 
<sac.khur...@gmail.com>
Subject: Re:Re: how to join like a "In A but not in B" operation?


Hi Sachin

I have tried LEFT SEMI JOIN, but the results are the same to a JOIN operation.

Richard


At 2012-03-12 12:03:39,sac.khur...@gmail.com wrote:
Hi richard,

You can use left semi join.

SELECT a.key.
FROM a LEFT SEMI JOIN b on (a.key = b.key)

Thanks,
Sachin
Sent from BlackBerry® on Airtel
________________________________
From: Richard <codemon...@163.com<mailto:codemon...@163.com>>
Date: Mon, 12 Mar 2012 11:52:11 +0800 (CST)
To: <user@hive.apache.org<mailto:user@hive.apache.org>>
ReplyTo: user@hive.apache.org<mailto:user@hive.apache.org>
Subject: how to join like a "In A but not in B" operation?

hi,

how can I join two tables A and B so that the result is "In A but not in B"?

let's take an example, say, the column to identify record is id.
e.g.

  select A.* from A join B on (A.id = B.id)

thanks.
Richard