AND field NOT IN(NULL) returns an empty set [duplicate]

Question

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.

Answer 1

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.

Answer 2

Comparing with null is always a bad idea. Even NULL = NULL or NULL <> NULL will return NULL.

Use IS NOT NULL insetad

In your case the query is identical to:

select p.iuser_id,p.produit_id from portefeuille p
WHERE produit_id=48
AND p.iuser_id <> NULL;

and the value of p.iuser_id <> NULL will be NULL regardless of the value of p.isuer_id and this explains why it returns an empty set!