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

[rudy]

>  SELECT tblChem.*
>   FROM tblChem LEFT OUTER JOIN tblMethods
>       ON tblMethods.SampleMethodKey
>              = tblChem.SampleMethodKey
>   WHERE (tblMethods.SampleMethodKey IS NULL)

hey sean

yes, that's how you get unmatched tblChem rows (although i personally would
mention the keys in the ON clause in the other order -- not that it
matters, it's just one of my code-it-so-it-doesn't-mislead conventions)

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

yes it should -- it may not be as efficient, but it should

DISTINCT will force a sort unless there's an index on that column

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

hmmm...

>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))

how can i put this?   ewwww!


>  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 )

the subquery selects 1 but it could just as easily select anything -- it
doesn't matter what it selects, because it either returns a result or it
doesn't, such that it evaluates true or false, and thus the "not exists"
evaluates false or true (if you know what i mean)


rudy
http://rudy.ca/
"if i don't see you no more in this world,
i'll meet you on the next one -- and don't be late"