T-SQL 101: 28 Working with NULL values (the lack of data) in SQL Server T-SQL queries

Look at the following query:

I’ve asked SQL Server to return orders where there are order comments. I did that by saying:

OrderComments IS NOT NULL

If I had said IS NULL, I would have returned all the orders with no comments. The interesting concept though, is NULL.

What is NULL?

The first thing to understand is that NULL isn’t a value. That’s why we can’t say:

WHERE OrderComments = NULL or WHERE OrderComments <> NULL

NULL represents a missing or unknown value. It’s not a particular value; it’s saying that the column has no value.

It’s really important to understand that this is not the same as having an empty string or a 0 value. If a string has no characters in it, it’s still a value. For example, if AddressLine2 is a string with no characters in it, that’s entirely different to not having a value for AddressLine2.

Being NULL is a state that a column can be in, rather than a value that it has.

Calculations with NULL

Now it’s important then, to start to understand that as soon as you have NULL involved in equations, things don’t exactly work like you might imagine.

Here’s a hint:

NULL does not equal NULL

The expression:

NULL = NULL

returns NULL, not true or false. So when you include this in a WHERE clause, things get messy. A WHERE clause predicate works when the expression is true. The expression in this predicate:

WHERE OrderComments IS NULL

can be true or false. This particular predicate would return all rows where there are no comments, but this expression in this predicate:

WHERE OrderComments = NULL

always returns NULL, not true or false, so no rows would be returned by that query.

Getting your head around NULL is a critical skill in working with the SQL language.

Note that there are options that allow you to change how NULL works and would make NULL = NULL return true, but don’t use those.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-07-29