What does it mean that a value is NULL?

11

July 14, 2014 by Kenneth Fisher

Let’s start by assuming that ANSI_NULLS are ON. If you aren’t sure what ANSI_NULLS are exactly, don’t worry, I’ll be going over that in some detail in a future post. However Microsoft tells us that ANSI_NULLS will always be ON in the near future. So we are not going to worry about that here.

So what does it mean to say that a value is NULL? Basically it means that the value is unknown. As an example, consider a binary variable. Its value can either be 1 or 0; but it can also be unknown (i.e. NULL). Pretty simple right? Well… on the surface maybe, but when you start thinking through the implications, it gets more and more complicated. For example:

DECLARE @Bin Binary
DECLARE @Bin2 Binary

SET @Bin = 1
SET @Bin2 = NULL

Here are the possible comparisons

  • (@Bin = 1) returns True
  • (@Bin = 0) returns False
  • (@Bin = NULL) returns NULL
  • (@Bin2 = 1) returns NULL
  • (@Bin2 = 0) returns NULL
  • (@Bin2 = NULL) returns NULL

 
Any value when compared to a NULL is NULL. Why? Well it’s kind of like Schrödinger’s Cat. Until a value is placed in it you don’t know what the value is. In fact, I like to mentally replace NULL with “unknown”:

  • (@Bin = unknown) returns unknown
  • (@Bin2 = 1) returns unknown
  • (@Bin2 = 0) returns unknown
  • (@Bin2 = unknown) returns unknown

 
Obviously, if a value is unknown, the result of any comparison is unknown. In other words NULL = NULL returns NULL.

Next example:

CREATE TABLE #NullTest (MyVal binary)
INSERT INTO #NullTest VALUES (1),(1),(0),(0),(NULL) 

SELECT COUNT(1) FROM #NullTest
WHERE MyVal = 1 -- Returns 2

SELECT COUNT(1) FROM #NullTest
WHERE MyVal = 0 -- Returns 2

SELECT COUNT(1) FROM #NullTest
WHERE MyVal = NULL -- Returns 0

So we get two rows where MyVal = 1, two rows where MyVal = 0 and no rows where MyVal = NULL for a grand total of four. But wait, we inserted five rows!! So as we go farther down the rabbit hole we start to realize that aggregate queries can be heavily affected by NULLs. The implications go farther and farther down the rabbit hole as you think about it. So how do we work around this? Well there are a couple of options.

ISNULL(MyVal,0) will return a 0 if MyVal is a NULL and return the value of MyVal otherwise. This leads to queries that look like this:

SELECT COUNT(1) FROM #NullTest
WHERE ISNULL(MyVal,1) = 1

I don’t recommend this for several reasons but the fact that it isn’t SARGable is probably sufficient.

Next we have the option “IS NULL”. Which is used like this

SELECT COUNT(1) FROM #NullTest
WHERE MyVal = 1
  OR MyVal IS NULL

This is generally the method I would use although you do have to watch out with your OR operator. I see a lot of logical mistakes when people use OR carelessly. Here is a simple example:

CREATE TABLE #NullTest (MyVal binary, DateVal datetime)
INSERT INTO #NullTest VALUES 
			(1, '1/1/1900'),
			(1, '1/1/1901'),
			(0, NULL),
			(0, '1/1/2001'),
			(NULL, '1/1/2000') 

-- What you meant
SELECT *
FROM #NullTest
WHERE 
	(MyVal = 1
	  OR MyVal IS NULL)
 AND 
	(DateVal > '1/1/1950'
	  OR DateVal IS NULL)

-- What you entered
SELECT *
FROM #NullTest
WHERE MyVal = 1
  OR MyVal IS NULL
 AND DateVal > '1/1/1950'
  OR DateVal IS NULL

-- How the computer saw it.
SELECT *
FROM #NullTest
WHERE 
	MyVal = 1
  OR 
	(MyVal IS NULL
	AND DateVal > '1/1/1950')
  OR 
	DateVal IS NULL

I’ll let you run it yourself if you want to see the different results, but just looking at the code you should be able to tell how easy it is to mess yourself up.

There are other methods of course, and I’m sure more will show up over time. The important thing to remember is that if you are going to allow NULLs in your columns then you need to understand what a NULL is and plan for it accordingly. If you don’t Thomas LaRock is liable to come after you (not as bad as Grant Fritchey coming after you if you aren’t taking your backups but still.)

About these ads

11 thoughts on “What does it mean that a value is NULL?

  1. Allen Nugent says:

    I used to think of NULL as meaning “nothing”, but that mislead me: a procedure didn’t work because I assumed that NULL = NULL. I then tried thinking of NULL as meaning “anything”, and the logic of NULL NULL became obvious.

    • Allen Nugent says:

      (This website deleted the greater-than, less-than signs from the above reply. That’s not logical!)

      • I’ve never thought of considering NULL as “anything” but that does work. I’ve always liked “unknown” but they work out about the same. Technically I think the definition is “unknown” but I could be wrong there :)

        The loss of the greater-than & less-than is probably an HTML thing. Can’t really help you there :)

  2. Juanita says:

    Great explanation of Nulls, as simple as they seem they can be tricky to use! Thank you for the examples

    • Thanks! Glad you liked the examples. That tends to be how I learn best so I try to throw them in where I can.

      NULL is one of the harder basic concepts to learn. It still catches me off guard occasionally.

  3. corey lawson says:

    NULLs are like “Seinfeld” episodes. Each episode was essentially about nothing, yet none of them are the same. They’re frustrating or pointless to watch for literal-minded people who have to have things mean something.

  4. corey lawson says:

    …I’ll add to my previous comment. Maybe it’s different on current versions of Oracle, but my last exposure was Oracle 10g. From Phillip Greenspun’s website (remember him? http://philip.greenspun.com/wtr/oracle-tips.html), I ran into this many years ago. It definitely made me go WTH? then…
    an empty string, ”, is the same as NULL.

    And, sure enough, so it was… Let’s be clear… ” isn’t equal to null, but equivalent (and indistinguishable) from null.

    Maybe (hopefully) there’s an option to twiddle this behavior in current versions of Oracle, but I always figured it was a bug in early coding of Oracle’s C code that just took on a life of its own so it couldn’t be fixed…

  5. […] I wrote about what it means that a value is NULL. Right at the beginning I mentioned ANSI_NULLS and said I would discuss it later. Well it’s […]

  6. Dom says:

    I have a question about your examples. In some you use “select count (1)”. I know what you are doing here … “1” never evaluates as null, so you are bypassing the way the “count” function does not count nulls. But isn’t the preferred way of doing this “Count (*)”?

    • Actually from what I have read COUNT(1) and COUNT(*) are evaluated the same way. In fact give COUNT(1/0) a shot some time. As I understand it the only time what you put inside the COUNT() matters is if it is a column name. In this example:

      CREATE TABLE #NullTest (MyVal binary, DateVal datetime)
      INSERT INTO #NullTest VALUES
                  (1, '1/1/1900'),
                  (1, '1/1/1901'),
                  (0, NULL),
                  (0, '1/1/2001'),
                  (NULL, NULL) 
      
      SELECT COUNT(MyVal), COUNT(DateVal) FROM #NullTest

      The results are 4,3 because the COUNT is counting non null values for just the column specified.

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 423 other followers

%d bloggers like this: