[thelist] sql brain jam--left outer join and NOT IN subquery
Sean G.
ethanol at mathlab.sunysb.edu
Sat Jun 22 13:12:01 CDT 2002
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.
More information about the thelist
mailing list