NULL values can sometimes confuse SQL developers when they first start to write queries. In this article, we’ll review some of nuances of NULL values that you should be aware of in your queries.

Imagine a table that has 50 records in it — one for each state. If we were to run a query such as the following, what would you expect the results to be?

SELECT COUNT(State) StateA_Cnt

FROM SomeTable

WHERE State LIKE ‘A%’

The results would return a count of 4. Easy enough. Now what if I asked for a list of all the states not starting with A?

SELECT COUNT(State) StateNotA_Cnt

FROM SomeTable

WHERE State NOT LIKE ‘A%’

Now you’ll get a count of the other 46 states. But what if for some reason there was a 51st record in your SomeTable that was NULL for the State field? What would the two queries above return?

You’d still get the same results for both queries! That means if you were to display both in a report and sum them up, you’d guess your table had the correct 50 records and all would be good and happy in the world. However, someone tells you a few months later that there are 51 records in your table. You show them the report and insist they are wrong. However, being a good developer, you dig into the table and sure enough find 51 records. That NULL value was not captured in either of your queries.

So how can you account for this NULL value? You decide you want those NULL values to appear in the 2nd query (does not start with A) and try the following:

SELECT State

FROM SomeTable

WHERE ISNULL(State, ”) NOT LIKE ‘A%’

However, you find your query no longer uses the Index Seek as it was before (now it’s using an Index Scan) so you know when this gets into production it will cause problems. Come to find out, the SQL Optimizer does not like functions. It is not able to produce a good estimate on how many records will be returned because the field it is using in the predicate (State) is now encapsulated by the function ISNULL(). How else can we write this?

SELECT State

FROM SomeTable

WHERE State NOT LIKE ‘A%’
OR State is NULL

Remember, just like with cats, there are always 20 ways to skin the proverbial query. Now with the logic for State split into the two statements it can use the Index Seek and is therefore more efficient and you get all the records. So now that you see a list of all 46 other states plus the NULL value, you add this line to your production code and tell the business owners the code is good and to please run tests to sign off.

SELECT COUNT(State) StateNotA_Cnt

FROM SomeTable

WHERE State NOT LIKE ‘A%’

OR State is NULL

However, they immediately call you back and tell you they still only see 46 records. Sure enough, when you check you still see 46. You double check everything and look at the full list with the SELECT State and see 46. You then try:

SELECT COUNT(State) StateNotA_Cnt, COUNT(*)

FROM SomeTable

WHERE State NOT LIKE ‘A%’

OR State is NULL

Guess what? You just discovered another nuance. When you do COUNT(*) you get a count of how many records there are, regardless of what is in the fields of that row. COUNT() with any field in the parameter rather than an asterisk will only count the values for that field. NULL has no value so therefore it does not count that record. So, to include this, you need to change it to COUNT(*) or count some other field on the row that could never be null.

Now what if you had a table that has many states in there (like an address book) and you wanted to know how many unique states are in your table? COUNT(DISTINCT State) would give you a count of distinct/unique states. However, just like COUNT(State), it will not count NULL values.

What other oddities or nuances have you seen with NULL values? Leave a comment below with your scenario!