问题描述
此查询为什么返回一个空集:
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)返回一个空集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！