RE: [SQLDownUnder] INNNER JOIN?

[Greg Low]

As I mentioned, I'd avoid using NOT IN in this case but if you do, I'd
suspect you'd be better off with DISTINCT as well because it will reduce the
size of the inner query return value.

Regards,

Greg 

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

Thanks Greg!  I just learned one more thing about SQL Server.

One more thing, do I need to use "distinct" in the sub-query if I use "not
in"?

-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Greg Low
Sent: Wednesday, 9 March 2005 1:41 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] INNNER JOIN?

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





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