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 <[EMAIL PROTECTED]> 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 | | ------------------------+---------------------------------------------+ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
