RE: [SQLDownUnder] INNNER JOIN?

[Greg Low]

Absolutely. Not In requires a list to be created in the sub-query. It would
be nice if the optimiser recognised it and made it the same but I wouldn't
want to depend on that behaviour.

EXISTS can exit as soon as anything that proves the sub-query is found. 

Another little note as well: In SQL Server, you don't need to specify a
column in the select statement in an EXISTS clause ie: it's the one
situation where SELECT * is considered "good form", unlike what occurs in
other databases.

Regards,

Greg

-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Junying Xue
Sent: Wednesday, 9 March 2005 12:20 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] INNNER JOIN?

I know for sure that there is a performance difference between "not in" and
"not exists" in Oracle.  How about in SQL Server?

-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of William Luu
Sent: Wednesday, 9 March 2005 11:25 AM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: Re: [SQLDownUnder] INNNER JOIN?

Thanks Aaron, that worked.

On Wed, 9 Mar 2005 11:16:06 +1100, Aaron Bull <aaron@xxxxxxxxxxxx> wrote:
<snip /> 
> select ItemID
> from Items
> where
> ItemID not in (select distinct FK_ItemID from Products)
> 
> Cheers,
> 
> Aaron
>


to unsubscribe to this list, please send a message back to the list with
'unsubscribe' as the subject. Powered by mailenable.com - List managed by
http://www.readify.net





to unsubscribe to this list, please send a message back to the list with
'unsubscribe' as the subject. Powered by mailenable.com - List managed by
http://www.readify.net




to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net