NULL is NOT LIKE and NOT NOT LIKE

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

SELECT COUNT(*) FROM Person.Address
-- 19,614 total records in the table

SELECT * FROM Person.Address WHERE AddressLine2 = NULL
-- ZERO Records Returned

SELECT * FROM Person.Address WHERE AddressLine2 IS NULL
-- 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 not LIKE 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.

SELECT * FROM Person.Address WHERE AddressLine2 NOT LIKE '%2%'
-- 19,553 Records EXPECTED
-- Only 301 Records actually returned

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 IS NULL

-- NOW Alter the table to disallow NULL
ALTER TABLE Person.Address
ALTER COLUMN AddressLine2 NVARCHAR(60) NOT NULL

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 bank to 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.

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

SELECT * FROM Person.Address WHERE ISNULL(AddressLine2, '') NOT LIKE '%2%'
-- 19,553 Records returned

This returned the expected results, but be warned, there are serious performance ramifications to using functions (e.g. ISNULL() ) in your WHERE clause, and better options exist.  Some recommended reading on that subject are the article Avoid enclosing Indexed Columns in a Function in the WHERE clause by Bill Graziano and the blog post Optimizing Conditional WHERE Clauses: Avoiding ORs and CASE Expressions by Jeff Smith.

P.S. For another interesting challenge with NULL, check out Peter Larson's NULL NOT IN Conundrum.

posted @ Monday, June 08, 2009 10:53 PM

Print

Comments on this entry:

# re: NULL is NOT LIKE and NOT NOT LIKE

Left by JohnFx at 6/9/2009 11:55 AM
Gravatar
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.


Good post.

# re: NULL is NOT LIKE and NOT NOT LIKE

Left by m at 6/9/2009 12:58 PM
Gravatar
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.

# re: NULL is NOT LIKE and NOT NOT LIKE

Left by RBarryYoung at 6/13/2009 6:11 PM
Gravatar
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".

# re: NULL is NOT LIKE and NOT NOT LIKE, re: Math

Left by RBarryYoung at 6/13/2009 6:17 PM
Gravatar
m: Yes, in Math identity and equality are usually the same thing, but in CS they are not. The CS concept of equality is claoser to the Math concepts of congruence or similarity.

# re: NULL is NOT LIKE and NOT NOT LIKE

Left by Saravanan at 6/15/2009 7:30 AM
Gravatar
You could also write an article related to this as how the columns with NULL values affect the performance of the INDEXES that you create on them.
Comments have been closed on this topic.
«April»
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910