AND字段NOT IN(NULL)返回一个空集 [英] AND field NOT IN(NULL) returns an empty set

问题描述

此查询为什么返回一个空集:

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);

而这个:

select p.iuser_id,p.produit_id from portefeuille p
WHERE produit_id=48
LIMIT 5

返回结果，例如

72968, 48
106967, 48
7381, 48
81678, 48
194250, 48

，并且这些值都不为NULL或应等于NULL. (为了简洁起见，我添加了限制5，并且我正在使用MySql 5.1)

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:在这里，我将问题缩小为NOT IN(NULL).原始查询有一个子查询，其中某些行包含NULL，如下所示:

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 )

和一些user_id为NULL，但是集合中只有一个NULL会污染整个查询.

and some user_id were NULL, but only one NULL in the set contaminates the whole query.

推荐答案

x NOT IN(...)被定义为x与子查询返回的每个值之间的一系列比较. SQL使用三值逻辑，逻辑表达式的三个可能值为 true ， false 或 unknown .值与NULL的比较为未知，如果那些NOT IN比较中的任何一个为未知，则结果也被视为未知

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.

这篇关于AND字段NOT IN(NULL)返回一个空集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！