NULL Is Not NULL

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

–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

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.