As you know SQL Server can hold NULL values in table’s columns. It usually means that a value of the column is unknown. For example, what is better to answer the question about on which floor does your friend live: on the 1st or 2nd, maybe 5th, N-th or just say “I’m afraid I don’t know exactly”. So NULL values can save this situation.
But when we try to match values we should be cautious about NULL values. Let’s look at the following examples.
I create two tables with sample data in tempdb.
So we have two matches by first name and one row in Clients table has no match. You can find matches with the help of subqueries starting with IN or EXISTS.
OK! We have the correct result. Now we want to find mismatches. Just add NOT before a subquery.
OK! It works correctly.
Now I will insert a new row into Clients table with no first name (there will be a NULL value in the column).
Match queries find the exact rows as shown above but the results of mismatch queries are different.
It’s somewhat surprising that first query with NOT IN could not find the row with NULL value. To include NULL values you can add additional comparison like OR FirstName IS NULL in ther WHERE clause.
The T-SQL code snippets can be downloaded from null_values_in_where_predicate
To sum up, I would recommend the following:
- Don’t use NULL columns if it doesn’t really needed
- Test thoroughly your queries for NULL/NOT NULL matches