Re: "IS NOT NULL" != "NOT NULL"

> > test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 NOTNULL;
> > count
> > -------
> > 3
> > (1 row)
>
> > test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 != NULL;
> > count
> > -------
> > 0
> > (1 row)
>
> These are not the same thing. See any of the past discussions about
> why "null = null" and "null != null" and so forth do not do what a
> novice might expect.

http://www.postgresql.org/idocs/index.php?functions-comparison.html

The archives appear to be broken, but the above link seems to explain
things well enough. I still can't grok the rationale as to why NULL
is interpreted as unknown and not interpreted as empty. I understand
that you can't compare two values that are unknown until the unknowns
are known. However why null isn't interpreted as empty is something I
haven't grasped. '' is different than empty ('' is a defined string
that's 0 characters in length), which is different than null
(unknown). In my mind: "col2 != NULL" is the same as "col2 IS NOT
NULL", but I fully understand why "col2 = NULL" is an invalid
statement. Not a biggie, just a source of curiosity. -sc

--
Sean Chittenden