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

"Jon Heggland" <heggland_at_idi.ntnu.no> wrote in message news:MPG.1df62f87b42a94ca989713_at_news.ntnu.no...
> In article <1133191499.952147.182080_at_z14g2000cwz.googlegroups.com>,
> boston103_at_hotmail.com 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