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

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

-----Original Message-----
From: Richard <[EMAIL PROTECTED]>
Date: Mon, 12 Mar 2012 11:52:11
To: <[EMAIL PROTECTED]>
Reply-To: [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

I used a left outer join with a constraint on a joined row column value being null -- logically equivalent to a "not in" clause.  Looks funny, but it works.

On Mar 11, 2012, at 10:52 PM, "Richard" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:

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

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

-----Original Message-----
From: [EMAIL PROTECTED]
Date: Mon, 12 Mar 2012 04:03:39
To: <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
Subject: Re: how to join like a "In A but not in B" operation?

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

-----Original Message-----
From: Richard <[EMAIL PROTECTED]>
Date: Mon, 12 Mar 2012 11:52:11
To: <[EMAIL PROTECTED]>
Reply-To: [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

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]>
Date: Mon, 12 Mar 2012 11:52:11 +0800 (CST)
To: <[EMAIL PROTECTED]>
ReplyTo: [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

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

this approach works, I did not realize "var=NULL" is different from "var is NULL" before.
 
Richard
At 2012-03-12 12:03:00,"Evan Pollan" <[EMAIL PROTECTED]> wrote:

I used a left outer join with a constraint on a joined row column value being null -- logically equivalent to a "not in" clause.  Looks funny, but it works.

On Mar 11, 2012, at 10:52 PM, "Richard" <[EMAIL PROTECTED]> wrote:
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

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" <[EMAIL PROTECTED]>
Date: Mon, 12 Mar 2012 05:43:09
To: '[EMAIL PROTECTED]'<[EMAIL PROTECTED]>; '[EMAIL PROTECTED]'<[EMAIL PROTECTED]>
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:[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