Re: So what's null then if it's not nothing?

From: vc <>
Date: 28 Nov 2005 08:01:27 -0800
Message-ID: <>

Jon Heggland wrote:
> In article <>,
> says...
> > Jon Heggland wrote:
> > [...]
> > >
> > > I prefer to avoid NULLs altogether. But as I'm sure you know, even SQL
> > > considers NULLs equal in many circumstances.
> >
> > Well, I do not know that. Could you give an example other than "order
> > by"
> Off the top of my head: Projection, and in some cases the UNIQUE
> constraint.

Re. projection. The projection definition uses the notion of set membership so that the issue of duplicates and therefore their elimination via comparison does not arise at the logical level either with respect to nulls or non-nulls.

The unique constraint behaviour depends on how you define it. The SQL'92 standard says that the column value is a duplicate if it's already present in the set of existing column non-nulls (the SQL'92 entry level does not allow unique constraints on nullable columns at all). So for NULL, the DUPLICATE predicate would return UNKNOWN (or NULL) which is then mapped to false -- you have to make some decision at this point. Of course, you can redefine the DUPLICATE predicate so that it would map UNKNOWN/NULL to true but that would be a non-standard behaviour which is also inconsistent with many other cases where the 3vl unknown is mapped to false. Interestingly, Microsoft SQL server does just that. Received on Mon Nov 28 2005 - 17:01:27 CET

Original text of this message