AND field NOT IN(NULL) returns an empty set
mysql, sql
Solution
x NOT IN (...) is defined as a series of comparisons between x and each of the values returned by the subquery. SQL uses three-value logic, for which the three possible values of a logical expression are true, false or unknown. Comparison of a value to a NULL is unknown and if any one of those NOT IN comparisons is unknown then the result is also deemed to be unknown.
Problem
Why does this query returns an empty set: ``` select p.iuser_id,p.produit_id from portefeuille p WHERE produit_id=48 AND p.iuser_id NOT IN (NULL); ``` whereas this one : ``` select p.iuser_id,p.produit_id from portefeuille p WHERE produit_id=48 LIMIT 5 ``` return results like ``` 72968, 48 106967, 48 7381, 48 81678, 48 194250, 48 ``` and none of those values are NULL or should be equal to NULL. (I added limit 5 for consiseness, and I am using MySql 5.1) edit: Here I narrowed the problem down to NOT IN(NULL). The original query had a subquery with some of the rows containing NULL as such: ``` WHERE user_id NOT IN( select user_id from mailsubscriptions ) ``` and some user_id were NULL, but only one NULL in the set contaminates the whole query.