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

[Sean G.]

Howdy,

Perhaps just because it's Saturday, I've got 2 queries I expect to return
the same results, but aren't.  I'm looking for records in one table that
lack a corresponding record in a second table.

I've been doing:
-----
SELECT 	tblChem.*
FROM 	tblChem LEFT OUTER JOIN
	tblMethods ON tblMethods.SampleMethodKey = tblChem.SampleMethodKey
WHERE 	(tblMethods.SampleMethodKey IS NULL)
-----

And that's working, returning stuff in tblChem without the appropriate
tblMethods record.

But shouldn't this give the same results:
-----
SELECT 	tblChem.*
FROM 	tblChem
WHERE 	SampleMethodKey NOT IN (SELECT DISTINCT SampleMethodKey from
tblMethods)
-----

?

Instead the query with NOT IN returns 0 rows while the LOJ query returns 24
rows.  (24 rows is the correct result.)

The following returns the same results as the LOJ query:
-----
SELECT 	tblChem.*
FROM 	tblChem
WHERE 	SampleMethodKey NOT IN
	(SELECT DISTINCT SampleMethodKey FROM tblChem WHERE SampleMethodKey in
		(SELECT DISTINCT SampleMethodKey FROM tblMethods))
-----

The LOJ works, and I think is better form, so that's what I'll be using, but
I can't figure out why the second query doesn't work while the third query
does--rather, I don't see why they return different results.

The only hint I have is the subquery
SELECT DISTINCT SampleMethodKey FROM tblChem WHERE SampleMethodKey in
(SELECT DISTINCT SampleMethodKey FROM tblMethods)

returns a bunch of records, while "SELECT DISTINCT SampleMethodKey from
tblMethods" returns the same records set plus one NULL record.


Any ideas??



TIA,


Sean G.