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@michvhf.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
==========================================================================