From: Roy Hann <specially_at_processed.almost.meat>
Date: Thu, 27 Dec 2007 10:37:18 -0000
Message-ID: <>

"stevedtrm" <> wrote in message
>> > If everyone is clear NULLS shouldn't be used, why the debate as to what
>> > to do about them ?
>> Because SQL allows NULL and even promotes the idea that NULL solves some
>> problem instead of introducing many.
> So everyone is agreed that NULLs shouldn't appear anywhere, and its
> just a matter of time before NULLS become a legacy problem and a
> relational language supercedes SQL?
> Are the two solutions I suggested before the widely accepted as
> resolutions to the two problems NULLs were introduced to eradicate?

It depends who answers the question. I am pretty sure that there are very few relational theoreticians who will defend the use of nulls today. Unfortunately there are very few relational theoreticians. On the other hand the overwhelming majority of practitioners enthusiastically embrace the use of nulls. There is a very large and growing number of practitioners. Since your question is about popularity, you can see the answer is obvious now.

I am a practitioner myself, and I feel pretty lonely discouraging the use of nulls. There are a variety of reasons why they must continue to be used in the short-term, mostly to do with the inadequacy of our programming tools. Those won't change for two reasons: programmers won't ask for what they don't know they're missing, and programmers seem not to want database tools very much anyway.

For the last few years I've been trying a different way of discouraging my colleagues from using nulls. I've noticed that almost all of the nullable attributes introduced into our systems are there to permit multiple fact types to be confused in one table. (There seems to be an intuitive desire to minimize the number of tables in a database. I don't know whether that's a psychological thing or whether it is something to do with reducing the amount of code that needs to be written.) Anyway, I have been able to show that decomposing these kinds of tables so that distinct fact types use distinct tables often incidentally produced very striking performance improvements too. I have been able to make the point out that these designs allow you to exclude nulls, and I have started to encourage them to look for that feature during design, as an indicator of future performance. By not over-selling the idea, and by starting with demonstrable benefits, I have won some sympathy. Once I have some sympathy I can make more complicated arguments. The other benefits of eliminating nulls have emerged automatically in time.

Outer joins continue to re-introduce nulls though. I'm not sure what to do about them.

Roy Received on Thu Dec 27 2007 - 11:37:18 CET

Original text of this message