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-value of "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(at)officenet(dot)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 | |
------------------------+---------------------------------------------+