NULL values inside NOT IN clause
Query A is the same as:
select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null
Since 3 = 3
is true, you get a result.
Query B is the same as:
select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null
When ansi_nulls
is on, 3 <> null
is UNKNOWN, so the predicate evaluates to UNKNOWN, and you don't get any rows.
When ansi_nulls
is off, 3 <> null
is true, so the predicate evaluates to true, and you get a row.
Whenever you use NULL you are really dealing with a Three-Valued logic.
Your first query returns results as the WHERE clause evaluates to:
3 = 1 or 3 = 2 or 3 = 3 or 3 = nullwhich is: FALSE or FALSE or TRUE or UNKNOWNwhich evaluates to TRUE
The second one:
3 <> 1 and 3 <> 2 and 3 <> nullwhich evaluates to: TRUE and TRUE and UNKNOWNwhich evaluates to: UNKNOWN
The UNKNOWN is not the same as FALSEyou can easily test it by calling:
select 'true' where 3 <> nullselect 'true' where not (3 <> null)
Both queries will give you no results
If the UNKNOWN was the same as FALSE then assuming that the first query would give you FALSE the second would have to evaluate to TRUE as it would have been the same as NOT(FALSE).
That is not the case.
There is a very good article on this subject on SqlServerCentral.
The whole issue of NULLs and Three-Valued Logic can be a bit confusing at first but it is essential to understand in order to write correct queries in TSQL
Another article I would recommend is SQL Aggregate Functions and NULL.
NOT IN
returns 0 records when compared against an unknown value
Since NULL
is an unknown, a NOT IN
query containing a NULL
or NULL
s in the list of possible values will always return 0
records since there is no way to be sure that the NULL
value is not the value being tested.