When using SQL not in function in the where clause, the null values are not returning in the result set

Question

I have created a simple test table to explain my case. Both the columns are varchar type.

When I run the query select * from test1 where TValue not in ('3','4') it returns the following 3 values only.

I am expecting it to return Sample6 value too. May I know why its returning only 3 values and what I need to do for it to return the Sample6 value too.

Answer 1

Because NULL doesn't work the same way as other value in "normal" comparisons like =, <, IN, etc, you need to use NULL logic as well:

WHERE TValue NOT IN (3,4)
   OR TValue IS NULL;

When comparing against a NULL using an expression like WHERE YourColumn != 1, where YourColumn has the value NULL, will result with the boolean expression returning NULL. The data engine will only return values where the boolean expressions return true, which NULL is not, and hence NULL != 1 will not result in the row(s) being returned.

Answer 2

The problem lies in the three-tiered logic inherent in SQL databases.

Any expression comparing a value against NULL returns a value of "unknown" -- which is distinct from a "true" or a "false" value.

  1 = 1           true
  1 = 0           false
  1 = NULL        undefined
  NULL = NULL     undefined (!)

Your query asks for records that meet the criterion "tvalue NOT IN (3, 4)". This will return a dataset in which the matching criteria are met. Since the NULL values comparisons do not return a "true" value (and not a "false" one, either), they are NOT included in your return set.

There are a couple workarounds for this behavior: the first is to explicitly test for null:

 WHERE tvalue IS NULL OR tvalue NOT IN (3,4)

The other is to use COALESCE to eliminate NULL comparisons:

 WHERE COALESCE(tvalue, 0) NOT IN (3,4)

For large tables, the first approach is probably the most efficient: comparing against an expression (i.e. the COALESCE expression) means that the expression has to be executed against every row in the underlying dataset.