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