A NULL Is Not Unknown

Like it or not, NULLs happen. I don’t think that’s I’ve ever seen a production database that didn’t have NULLs.

Waiter, there's a NULL in my soup.

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.

I'm just not myself without a mani-pedi.

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.

Related Posts

About these ads

13 Responses to A NULL Is Not Unknown

  1. David Stein says:

    Great post Joe, but aren’t you arguing semantics? To me, a Null is an unknown value because it is unknown to the database. Isn’t that really the same as an absence of a value?

    • Joe says:

      In a sense, yes. But there are a lot of people who use the term NULL Value and believe that NULL is actually a value, that NULL is a string, int, etc, when it’s not.

      The post actually started out to be a straight T-SQL post on NULLs, but the preliminaries grew into a post by itself.

    • Joe Celko says:

      The amont of gasoline in my Toyota is zero gallons; This is a measurement on a scale of an attribute of an entity. And I wish my wife understood that “E” does not mean “Enough”.

      The color of the feather of my Toyota is NULL. The entity exists, but does not have that attribute.

      The amont of gasoline in my Lamborghini is NULL. The entity does not exist to have the gasoline tank attribute.

      Codd made a distiction between these two kinds of NULLs in the Second Version of the Relatioal Model.

  2. Whew! Even if you’re avoiding the null good/bad debate, it’s still a topic that takes a bit of bravery to tackle. Looking forward to the next articles.

  3. Pingback: Dew Drop – February 12, 2011 | Alvin Ashcraft's Morning Dew

  4. Pingback: Weekly Link Post 182 « Rhonda Tipton's WebLog

  5. David McKinney says:

    Reminds me of comments by a certain Donald Rumsfeld regarding WMDs.

    There are known knowns; there are things we know we know.
    We also know there are known unknowns; that is to say we know there are some things we do not know.
    But there are also unknown unknowns – the ones we don’t know we don’t know.

  6. Gale says:

    Very nice refresher…we tend to forget these things as we swim the swamps of sql. Too bad NULLOLOGY 101 isn’t tomorrow. I was really getting into the article! Write on, Joe. Great easy (and interesting) style.

  7. Pingback: I Don’t Know! Deal With It! (Nulls) | Made2Mentor

  8. SQLguru says:

    The confusion the author has is between an “Unknown” at a state in time and “Unknown” being used as a value as provided in his example.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 32 other followers