[thelist] sql brain jam--left outer join and NOT IN subquery

Sean G. ethanol at mathlab.sunysb.edu
Sat Jun 22 21:35:01 CDT 2002


Howdy,

Yup.

SELECT     tblChem.* FROM         tblChem
WHERE tblChem.SampleMethodKey NOT IN
	(select distinct SampleMethodKey from tblMethods
		where SampleMethodKey is not null)

Actually returns the same results as left outer join "WHERE (tblMethods.SampleMethodKey IS NULL)"--though I'll use the join where
possible over the NOT IN because of the extra nested loop to check each key against the results of the sub-query.

Though now I'm thinking the method you suggest is even better than the join.  The actual execution plan will be very similar, except
you don't have that filter, "WHERE (tblMethods.SampleMethodKey IS NULL)".  Not a huge savings in processing power--perhaps a couple
of % of the total procedure--but every bit helps  =)

BTW, I'm running all this in MS-SQL 2k, if that matters--and it probably does.

Thanks!


Sean G.

"I didn't mean to take up all your sweet time
I'll give it right back to you one of these days."


<tip type="RDBMS Admin" name="Oracle and MS-SQL command line utilities">
MS-SQL has a command-line utility similar to (yet very different from) Oracle's SQL*Plus, useful for running batch jobs and such.
More info at:
<http://www.microsoft.com/TechNet/prodtechnol/sql/proddocs/utilref2/chpt4/75524c16.asp>


Oracle has a utility to run queries from the command line similar to (yet more useful than) Microsoft's Osql.  More info at:
<http://otn.oracle.com/tech/sql_plus/content.html>
</tip>


-----Original Message-----
 <snip>

>  SELECT DISTINCT SampleMethodKey from tblMethods
>  returns the same records set plus one NULL record.

this includes a null?  uh oh, you might gots a problem

maybe you have 24 rows with actual null keys?

they'd all collapse into one in a DISTINCT query


here's another way to get unmatched rows, and it might be more efficient
than the NOT IN (efficiency really depends on which database you're running
and whether you have indexes declared on the appropriate columns)

this involves a correlated subquery --

   select tblChem.* from tblChem
  where not exists
           ( select 1 from tblMethods
               where SampleMethodKey
                 = tblChem.SampleMethodKey )






More information about the thelist mailing list