I agree, and that's why I recommend learning how to use NULL properly and using it in moderation. Saying "Get rid of all the NULLs!" is easy; actually doing it is another matter.

RM expert Chris Date recommends eliminating NULLs completely, and one of the main tools in his arsenal is the Default. Sounds good in theory - eliminate all NULLs by just plugging in an arbitrarily chosen value. But it has its drawbacks. Consider the following table:

Just about any number you could come up with for a default temperature in this table would be wrong, and produce incorrect results. If we don't know the temperature readings on July 30 - August 1, we just don't know them; and no Default value will ever fix that.

Imagine what the average temperature for this time period would be if you plugged in 0 as a Default. How often does the temperature drop from 97 degrees to 0 degrees, and then jump back up to 102 degrees in July and August? Probably not too often. Of course you could plug in another default, say 100, but it will be strange (to say the least) to watch your temperatures jump from -10 degrees up to 100, and back down to -6 in January. Or you could use another throwback to the "magic number" era (-9999999, etc.), but then you still need to put special handling for that value in your queries; and the magic number for this table might not be the same one used in another table. You have to define your own special "behaviors" for these "magic numbers."

Same thing goes for dates in the database. One of the main reasons for the existence of NULL is to eliminate "magic numbers" and arbitrary placeholders with a single "special mark" that has a well-defined behavior. Whether or not you like, or agree with, the "definition" and "behavior" that ANSI has come up with for NULL is another discussion...

I would recommend staying away from "magic numbers" and arbitrary special markers. You might have noticed the subtle "bug" I introduced in the third query of "Step Two". In that query there are two arbitrarily-defined special markers for employees with "no contact number type", but they appear in the same huge query:

• 'NO CONTACT NUMBER DESCRIPTION'
• 'NO CONTACT NUMBER TYPE'

Now the client application has to deal with both of these special markers in a single column, even though they mean exactly the same thing.

Using a "magic date", like "1900-01-01" can cause problems as well.  While "1900-01-01" might be considered by some to be reasonable as a substitute for NULL for a date in the future (like prisoner release date), it might not work as well for a date in the past (prisoner birth date); especially if you need to store historical data.  You'll suddenly need to use two or more "magic dates" as substitutes for NULL in different columns of your table(s).  It can turn into a real mess, really fast.

These are just a couple of the problems you can encounter when trying to use arbitrarily-defined special markers, "magic dates", and "magic numbers" in your database.