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:[email protected]] Sent: Monday, March 12, 2012 12:26 AM To: [email protected] <[email protected]>; [email protected] <[email protected]> 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,[email protected] 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 <[email protected]<mailto:[email protected]>> Date: Mon, 12 Mar 2012 11:52:11 +0800 (CST) To: <[email protected]<mailto:[email protected]>> ReplyTo: [email protected]<mailto:[email protected]> 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
