Re: simple SQL query

On Wednesday 29 October 2008 21:56:14 Kevin Duffy wrote:
>
> Gentlemen:
>
> Thanks so much for your assistance.
>
> This returns 512 rows.
> select * from tmp_index_member tim
>   where tim.ISIN  NOT IN
>   (select ISIN from security sec
>      where  ISIN is NOT NULL and
>        securitytypekey IS NOT NULL and  securitytypekey NOT IN ( 5,27) )
>
> Can someone explain why the NULL ISINs in Security is causing
> so much grief?  I do not get it.

Sure. BTW; I ment "IS NULL OR securitytypekey NOT IN (5,27)".
Remember that "WHERE col NOT IN (<list>)" doesn't match NULL-values for "col", so these will both return "false" for
NULL-valueof "col": 

WHERE col NOT IN (2,3)
WHERE col = 2

The reason is that NULL is "unknown", so testing against it also returns "unknown"(NULL).

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CEO
------------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Karenslyst Allé 11      | know how to do a thing and to watch         |
PO. Box 529 Skøyen      | somebody else doing it wrong, without       |
0214 Oslo               | comment.                                    |
NORWAY                  |                                             |
Tlf:    +47 24 15 38 90 |                                             |
Fax:    +47 24 15 38 91 |                                             |
Mobile: +47 909  56 963 |                                             |
------------------------+---------------------------------------------+