Like it or not, NULLs happen. I don’t think that’s I’ve ever seen a production database that didn’t have NULLs.
I’m not arguing whether or not NULLs in a database are good or bad. That discussion ranks up there with religion, politics, and naming conventions. My point is that NULLs exist. They are a fact of life and we must understand them conceptually before we can handle them programmatically.
So, What Is A NULL Anyway?
Let’s start with a definition. NULL is a convenient way to represent the absence of a value. It is not zero in the case of numeric columns. It is not a zero-length string in character columns. It is most assuredly not 1900-01-01 in a date column.
A NULL means that no value has been supplied. There is no value. There is no string; there is no number; there is no date. There is only the absence of a value.
But Doesn’t NULL Mean An Unknown Value?
It’s true that we don’t have an actual value for NULL. But that doesn’t necessarily mean the value is “unknown”. Let me illustrate with an example.
Consider a web site where pet owners make appointments with a Pet Cosmetologist. (I know; just go with it.) The web site asks the pet’s name, species, etc. One of the questions is the pet’s gender. For some animals, this is a very straightforward question. For other animals, answering that question takes uncommon skill.
The web developers recognized that some pet owners may not know Puddle’s gender so they provided three options in the drop down list: Male, Female, and Unknown. That should handle most every case. Dog owners can enter Male or Female as appropriate. Owners of kittens can enter Unknown.
Gender is not a required field on the web site. The owners of the Pet Boutique don’t want to turn away an appointment request just because the pet’s owner doesn’t answer a question. Thus, owners of kittens who are embarrassed by their sexing ineptitude can leave that answer blank.
See the difference? When the pet owner enters Male or Female, we know the gender of the animal. If they enter Unknown, we have a value provided by the owner. We don’t know whether to expect a boy or girl, but we have been given an answer to the question.
If the non-required question is left blank, we may end up with a NULL in the database. So NULL is the absence of a value altogether. It’s a question unanswered. Of course, the web developers may default to “Unknown” but that, in itself, may be misleading since it was not provided by the user.
Next Up: Nullology 101
If NULLs permeate our databases, it’s critical that we understand their affect. For example:
- What happens when we sum a column that contains NULLs?
- What happens if we concatenate strings that contain NULLs?
- Can a NULL equal another NULL?
I’ll tackle these questions and more in the next post.
- Removing Time From A Datetime Value
- Adding A Column To Every Table
- Where Is The SQL Server ErrorLog File?
- When Were The Statistics In SQL Server Last Updated?
Filed under: Administration, SQLServerPedia Syndication, T-SQL