Re: NULL versus NOT NULL usage

IMTB Data Management (Rick Gebethner) wrote:
>
> RABK wrote:
> >
> > I am researching the benefits of NOT NULL versus NULL datatypes and am
> > interested in the viewpoints of the exp dba, client developers.
 

> [...]
> A big question here is 'what does a null value mean?'. I believe it was
> C.J. Date who, a number of years ago, came up with something like 27
> different possible meanings for a null column (i.e. there was no value
> available, the value was in error, it was deleted, it was not
> applicable, etc). You'll have to consider how nulls fit with your
> business rules.
>
> One area where nulls are very useful is in aggregates and arithmetic
> functions. If you do an average of a number of rows, for example, the
> sum gets divided by the number of not-null values - very different than
> if you load up unknown/unapplicable fields with zeroes.

I would like to note that Ch.Date changed his mind concerning nulls, see e.g. the paper "Oh No Not Null Again!" (Date & Darwen, Relational Database Writings 1989-91, Addison-Wesley 1992.) The opinion from this paper (and several other papers): "...the null value concept is far more trouble than it is worth...". He gives many striking examples of anomalies implied by null values. For example, "ofically" every null is distinct, hence null=null should return false, but operators "distinct", "group by" and "order by" treat nulls as identical, and aggregate functions totally ignore null values. Another his example: if columns A and B can contain nulls, then in general

      select sum(A) + sum(b) from R
and

      select sum(A+B) from R
can return different results.
These are only few examplex of paranoic solutions cocerning nulls. Date concludes that these inconsistences are not only the property of inadequate SQL design, but they are inevitable consequence of the idea of null values. He proposes to avoid nulls, and instead, to use default values.

If you want to know a bit more about the topic, read my paper "Processing Semi-Structured Data in Object Bases" available via http://WWW.ipipan.waw.pl/~subieta/EngPapers/semistru.pdf

Regards
Kaz Received on Tue Jun 02 1998 - 00:00:00 CEST