NULL Is Not NULL

April 7, 2009 at 7:55 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , )


NULL as a concept seems so easy but it leads to so many problems for people. To put it as simply as possible, NULL does not equal anything. It does not “not equal” anything either. It can’t be compared to other values in any way. There was a recent post over at SQL Server Central where the user had a query problem that one of the great people over at SSC solved, handily. They also pointed out that the bit of code being used “WHERE nt.NullableString NOT LIKE ‘null%’” was also a problem. The user insisted that it was eliminating the NULL values. Well, yeah, sort of, it was, but not because it was actually applying a filter to the NULLs. Remember, a NULL does not equal or “not equal” anything and therefor when checking for the equality of something you won’t get NULL values.

Yeah, you’re thinking, so what’s the problem with the code then? This. It can lead to problems with your results. Here’s some sample code that illustrates the problem:

 CREATE TABLE #NullTest
(ID INT IDENTITY(1,1),NullableString NVARCHAR(50) NULL)

–I put the – next to the string NULL so you can see which one it is.
INSERT INTO #NullTest (
NullableString)
SELECT ‘Some Value’
UNION
SELECT
‘NULL-‘
UNION

SELECT NULL
UNION 
SELECT ‘NULL values are not allowed’
UNION 
SELECT ‘NOT NULL’

–returns the five rows available
SELECT * FROM #NullTest AS nt

–returns only two rows, although two other rows should be returned
SELECT *
FROM #NullTest AS nt
WHERE nt.NullableString NOT LIKE ‘null%’

–instead of returning three values, it only returns two, as it should
SELECT *
FROM #NullTest AS nt
WHERE nt.NullableString LIKE ‘NULL%’

— the real values that are not NULL, four of them
SELECT *
FROM #NullTest AS nt
WHERE NullableString IS NOT NULL

DROP TABLE #NullTest

As you can see, the wrong number of rows are returned depending on how the query is used. There are four rows in the table that are NOT NULL (notice the syntax) but running the query the other way only returns two values. There are ongoing debates about the utility, meaning and purpose of NULLs. I’m not getting into it. If you’re using them, you need to understand what they are and what they are not.

4 Comments

  1. SQLBatman said,

    i hate NULLs.

  2. Jack Corbett said,

    Nice post. I wasn’t in on the thread you mention, but I was in another one recently.

    Unlike SQLBatman I don’t hate nulls.

  3. scarydba said,

    Actually, I kind of like NULLs too. But you have to know what they are and how to work with them or they will chew on you quite severely.

  4. Aaron Alton said,

    Don’t get me started, Tom! ;-)

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

%d bloggers like this: