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

From: David Cressey <>
Date: Tue, 29 Nov 2005 14:00:42 GMT
Message-ID: <eaZif.6706$>

"Jon Heggland" <> wrote in message
> In article <>,
> says...
> >
> > 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.
> Sophistry. A SELECT DISTINCT (it is really too kind to call this SQL
> construct "projection") eliminates duplicate NULLs, just like it
> eliminates duplicate values. To handwave this by saying "well, they're
> not *distinct*, but that doesn't mean they're *equal*" is just a bad
> excuse.

It comes as a surprise to me to learn that the SQL standard does not specify clearing NULLS from DISTINCT results. I agree that it's unfortunate, if true.

There *is* a workaround. You can always throw in a restriction, like

      WHERE XXX IS NOT NULL I admit that this has the flavor of a kluge, but then so does the Pick construct of:

      age > 90 and age #

In both cases programmers are working around the language rather than with the language.

> I suppose sorting is defined in a similar manner? "NULLs are collected
> at the top in an ascending sort, and at the bottom in a descending sort,
> but we don't actually *compare* them to anything. Oh no."
> > The unique constraint behaviour depends on how you define it.

Sorting pertains to cursors (read: lists) rather than sets. If a select forms a set with a NULL in it, then I expect ORDER BY to put it in the cursor somewhere. Received on Tue Nov 29 2005 - 15:00:42 CET

Original text of this message