Comparisons with NULL

Or ‘True, False or FileNotFound’ ⁽¹⁾

Null seems to confuse a lot of people. The main issue with null is that it is not a value. It’s the absence of a value, a place holder for unknown data. Hence, when it is used in a comparison, it returns results that look surprising. Take this as a quick example

DECLARE @Var1 INT
SET @Var1 = NULL

SELECT
CASE WHEN @Var1 = NULL THEN ‘Var1 = Null’ ELSE ‘Var1 <> NULL’ END AS EqualityTest,
CASE WHEN @Var1 <> NULL THEN ‘Var1 <> Null’ ELSE ‘Var1 = NULL’ END AS InequalityTest

The results look a little strange. They’re supposed to. (No, I didn’t mean IS NULL/IS NOT NULL)

SQL uses what’s know as three-state logic. A boolean expression can be true, false or NULL. If any of the values in the expression are NULL, it will return NULL. This has interesting implications for conditions in a query.

Let’s take a simple table that has a value column with numbers from 1-10 and null (creation code at the end of the article)

A condition WHERE Value = 5 will, as expected, only return the rows where value = 5
A condition WHERE Value <> 5 will return the values 1-4 and 6-10, but not NULL.
Only a condition of WHERE Value IS NULL will return the null row. Anything else eliminates the null row.

Why is this so important? Well, often I see code like the following:

SELECT …
WHERE ISNULL(SomeStringColumn,”) <> ” ⁽²⁾

Looks fairly simple. Returns all rows that don’t have null or empty string in the string column. Fair enough, it does work. There’s one big problem with that though. The function on the column prevents index usage (assuming there’s an index on the column). It’s also totally unnecessary.

As shown above, any condition on a column (except IS NULL) will eliminate null rows. So that query could be expressed as

SELECT …
WHERE SomeStringColumn <> ”

and it will return exactly the same values, will allow effective index usage, and won’t force the CPU to do unnecessary computations.

(1) What is Truth?
(2) Thank to Jeff Moden from SQLServerCentral.com who initially brought this query and issue up.