NULL Values In WHERE Predicate of SQL Queries (IN, NOT IN And EXISTS). How to Avoid Wrong Results

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.

data
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.
matched rows
OK! We have the correct result. Now we want to find mismatches. Just add NOT before a subquery.
unmatched rows with null
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).
data with null
Match queries find the exact rows as shown above but the results of mismatch queries are different.
unmatched rows with null
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:
  1. Don’t use NULL columns if it doesn’t really needed
  2. Test thoroughly your queries for NULL/NOT NULL matches

Leave a Reply

Your email address will not be published. Required fields are marked *