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

On Sat, 19 Jan 2002, Sean Chittenden wrote:

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

I had a hell of a time with that at first too. What you need to
understand is that NULL isn't necessarily empty as you would expect.
It's not the same as a null string - a null string actually has a
real definition, a zero length string. I probably didn't help much.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev(at)michvhf(dot)com http://www.pop4.net
56K Nationwide Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================