Bringing Business Sense to the IT World...

This is one of those things that you probably know, but have not run into it lately. That was the case for me recently as I was troubleshooting an issue in someone else's code, and spent several hours on it before stumbling across this cause.

The issue at hand is that people forget about the true nature of NULL in a database column. Another way of thinking about NULL is to substitute the word UNKNOWN. NULL is not anything...it is unknown. NULL does not equal anything. That is why you have to use the magic phrase IS NULL instead of = NULL in your SQL queries:

USE AdventureWorks
SELECTCOUNT(*) FROM Person.Address
-- 19,614 total records in the tableSELECT * FROM Person.Address WHERE AddressLine2 = NULL-- ZERO Records ReturnedSELECT * FROM Person.Address WHERE AddressLine2 ISNULL-- 19,252 Records Returned

And not only is NULL not equal to anything, it is not LIKE anything either. That's pretty easy for people to grasp, once they have bought into the equals issue...

SELECT * FROM Person.Address WHERE AddressLine2 LIKE'%2%'-- 61 Records returned

Notice that none of the rows returned have NULL in the AddressLine2 column because NULL is notLIKE 2. But what trips up some database developers, and was at the root of my trouble today, is that while NULL is not LIKE anything, it also is not NOT LIKE anything either.

In the above example, the developer was expecting the inverse of the LIKE clause, that is, he expected the other 19,553 records to be returned, but if you look at the actual results, you see that what you get are only the records that have a value (NOT NULL) and that value is not like the search value. All of the records that have a NULL AddressLine2 are excluded from the result set.

How do you fix it?

There are a few different approaches you could take to fixing it. My first suggestion would be to disallow NULLs in the column in the first place.

-- When removing NULL from an existing table, first UPDATE the table with values.UPDATE Person.Address SET AddressLine2 = ''WHERE AddressLine2 ISNULL-- NOW Alter the table to disallow NULLALTERTABLE Person.Address
ALTERCOLUMN AddressLine2 NVARCHAR(60) NOTNULL

Now don't get me wrong, I'm not one of those guys who forbids NULL in his databases. I'm like the ability to have NULL in many situations. To me there is a certain amount of truthiness to saying, "Hey, I don't know what the value is." instead of just using an empty string or zero (especially zero...zero is a real value...there's a big difference between having zero dollars in your savings account and not knowing how much is in savings). On the other hand, there are times when a column absolutely must have a value. It is NOT acceptable for the bankto not know how much is in my savings account, for example.

So, if you are going to still allow NULL in your database, then you need to interpret or convert NULL on the fly using ISNULL or a CASE statement, or some other related approach.

Comments on this entry:

If I only had a nickel for every time a junior programmer came to me complaining that SQL Server had a bug because SELECT * FROM MyTable Where (MyField<>"Somevalue") doesn't return rows where MyField is null.

Great story. That 'IS NULL' has a different meaning than '= NULL' is so pedantic that it makes me want to hurl. In math, a IS b, is equivalent to a == b, and therefore a = b. SQL is a formal language and a fashionable language but neither a logical nor concise language.

Nice article, but I think that your title would work better if you capitalized it the way that you did in the text of the article. That is, "NULL is not LIKE and not NOT LIKE" instead of "NULL is NOT LIKE and NOT NOT LIKE".