A lot of values have been associated with NULL – zero, +/- infinity, empty string, +/- forever, and others. So which value is right? I would argue none of these. The only value that should be associated with NULL is… NULL, or rather, nothing – unknown. Using NULL to mean anything else is confusing, inaccurate, and complicates queries later on. Imagine you have records that are valid from a date and to a date, and use you NULL to represent forever in the to date. If you do this, you will have to do NULL checking anytime you want to grab all the currently active records, or any time you want to check the active records between two dates. Not only is this more difficult to work with, but it is also less efficient. Another common situation that comes up is using NULL instead of zero. When you do this, it means that anywhere you display the value to an end user you have to check for NULL and convert it to zero for display. But perhaps the most important reason not to use NULL to represent another value is that when you do that you cannot know if the person who entered that record meant forever or zero, or if what they meant was they don’t know. If I tell you that I have zero apples versus if I tell you that I don’t know how many apples I have, I am telling you two very different facts.
Categories