Re: very big problem with NULL

Lee Harr wrote:

> On Thu, 07 Jun 2001 19:11:21 -0400, Grant <grant@xactcommerce.com> wrote:
> > ok.  i've found a weird bug.  I have five records in a table.  in one
> > column i'm doing a select based on, two values for the column are NULL.
> > i do a 'SELECT testcolumn FROM testtable WHERE testcolumn!=1'.  This
> > query for some reason also excludes NULL, which does not make any sense
> > considering NULL is not equal to 1, so the records with NULL in this
> > column should be showing up as well.
>
> The thing about NULL is ... you just don't know!
>
> Maybe the column is number of children and sometimes when people
> are entering data they forget to ask that question and so don't
> enter that data (and you allow that, by not marking the column
> NOT NULL) so some of the records have a "value" of NULL.
>
> That does NOT mean that those people don't have 1 child.
>
> Therefore, when you want those people which DEFINITELY DON'T
> have 1 child, those records are not returned.
>
> Now, if you want the records which don't have 1 child or which
> you don't know how many children there are, you can do that.

so what you're saying is NULL = * in Postgres??  With every other database
server I've used, NULL is its own value, not any value, or I'm completely
misunderstanding what you're trying to say here.

However, the last time i checked,  NULL does not equal to 1, 2, 3, or
anything else besides NULL.  Thats why I'm confused as to why SELECT column
FROM table WHERE column != 1 also excludes NULL, because 1 does NOT  have the
same value as NULL.