There was a blog post a while ago that declared that NULL was the worst ever mistake in history of computer science. The fact that nulls existed meant that code always has to be written to handle them, otherwise programmes would crash with Null Pointer Exceptions or their Windows equivalent Object Reference Not Set To An Instance Of This Object. Life would be easier and less error-prone if there was no such thing as NULL.
It may have a point when it comes to object-orientated programming languages, but when comes to databases, the suggestion that the concept of NULL is a dangerous bad idea is nonsense. The fact that some common beginners’ mistakes involve a lack of understanding over how to use NULL does not change this.
NULL has meaning.
Precisely what it means is dependent on the context, but it still has meaning. Consider an End Date. What does it mean if you have no End Date? It means, perhaps, that you are still employed, or maybe even still alive. Defaulting to some arbitrary date far, far into the future in this context is nothing but an ugly kludge. You still have to write code to handle that value, and you’ve probably got a greater potential for error than if you’d just used NULL.
NULL has meaning.
Another example. Imagine a stock control system. Each stock item has a re-order level. When the stock level reaches this point it will trigger an automated re-order process.
Now imagine there are some stock items where you want to keep track of how many you purchase and use, but you don’t want to hold them in stock; perhaps the value is high and lead time is low, and you don’t use them frequently enough to want that much capital tied up in inventory. You will only raise purchase orders manually when you know you’re going to need the thing.
So most of the time the stock level is going to be zero. But you don’t want that automated re-order process to touch it. What value do you give the re-order level?
Now, you could set it to -1, and one stock control system I know did precisely that. But that’s still a kludge, and there are scenarios that will break it.
What if the stock transactions are transmitted via a mobile network that’s not 100% reliable, which means they’re not guaranteed to be real-time and may be received out of sequence? Again, I know a system that works like this. For one thing you can’t enforce a database constraint that makes sure the stock quantity never goes negative. It’s possible that you might end up with a stock level of -1 for a short period because a receipt transaction has been delayed by an electronic sargasso. Then there’s a window in which that pesky re-order process could turn up and do mischief.
If you’d just let the re-order level be NULL for those items you only want to re-order manually, you would not have that potential bug.
This is why we have NULL.