I know we've all beaten the discussion of NULL to death on this blog, but I had an epiphany of sorts when flying to Germany for the European PASS conference. Thus, I'm going to pick up the topic just one more time...
It occurred to me that the word "Null" has a very specific meaning depending on the language you are speaking - TSQL, "academic"-SQL, English or...German, where null literally means zero. Thus, I would naturally expect some confusion when seeing the word "NULL" come up in a result if it is shown to your average German users.
That brings me to what I think is the fundamental problem with the term NULL. In a very practical sense in SQL Server NULL is:
- A recording in the NULL Bitmap for a specific row that a specific field (a tuple in the academic lingo) has the property of NULL, i.e., the tuple has a property, it has nothing to do with the value or lack of value in the column
The problem is that language comes in to play. No one likes trying to explain NULL to users in an academic sense, so we come up with definitions. Here are some of the more popular ones (correct or incorrect):
- "The absence of value" - I think Codd would have approved of this, as I think C.J. Date and others would endorse. The problem I have with this definition is that there is a connotation with this phrase. To me (and others that I've experienced), the implication is "Why is there the absence of value? Did someone ignore the field? Did the clear it out deliberately? Is it not applicable? Did the consumer not supply an answer to the question?" Thus, NULL chaos ensues, where people take it to mean whatever the conceive of (regardless of the intent of the DBA or developer). Here we get reports supplying questionable information.
- "Empty Set" - I wouldn't agree that this is an accurate definition, but again it implies - "Why is the set empty?" There must be some reason why there is a lack of data within the "tuple". Thus we get the practical affect of converting NULL to an empty string ('') or a zero (0). Was the logic of the NULL transferred properly from the DBAs and Developers to the end user - no. Again, what I call NULL chaos ensues.
- "Nothing" - Again I would disagree a bit with the definition, but the connotations remain the same - "Why is it nothing? Did no one enter data? Did they remove data? Is the "tuple" not applicable?" All of these things come to mind naturally to me (althought one could question whether I'm an accurate representation of a "normal" person).
- "Unknown" - As far as SQL Server goes, this is the best definition. The connotations are almost child like - "It is unknown. - Why is it unknown? - I don't know why....Why doing you know why?...ad infinitum". "Unknown" is also a very practical definition when it comes to understanding things like mathematics where NULLs are involved (6+NULL=NULL -->6 + "unknown" must equal "unknown"
In any event, we need to keep in mind as database professionals that the actual description used for NULL will naturally carry with it specific connotations, for good or for bad. Language plays a great role in the understanding and ultimate usage of NULL - let's remain aware of that.