SQL查询问题:SELECT ... NOT IN

我确实犯了一个愚蠢的错误,但我无法弄清楚:

在SQL Server 2005中,我尝试选择所有客户,除了那些在凌晨2点之前预订的客户.

当我运行此查询时:

SELECT idCustomer FROM reservations 
WHERE idCustomer NOT IN 
  (SELECT distinct idCustomer FROM reservations 
   WHERE DATEPART ( hour, insertDate) < 2)

我得到0结果.

但

SELECT idCustomer FROM reservations 

返回152.000结果和"NOT IN"部分:

SELECT distinct idCustomer FROM reservations 
WHERE DATEPART ( hour, insertDate) < 2

仅返回284行

SELECT distinct idCustomer FROM reservations
WHERE DATEPART ( hour, insertDate) < 2
  and idCustomer is not null

确保list参数不包含空值.

这是一个解释:

WHERE field1 NOT IN (1, 2, 3, null)

是相同的:

WHERE NOT (field1 = 1 OR field1 = 2 OR field1 = 3 OR field1 = null)

最后一次比较评估为null.

该null与布尔表达式的其余部分进行OR运算,产生null.(*)

null被否定,产生null.

null不为true - where子句仅保留true行,因此所有行都被过滤.

(*)编辑:这个解释非常好,但我想解决一件事,以避免未来的挑选.(TRUE或NULL)将评估为TRUE.例如,如果field1 = 3,则这是相关的.该TRUE值将被否定为FALSE并且该行将被过滤.

它总是危险的,有NULL在IN名单-它往往表现为预期的IN,但不是为NOT IN:

IF 1 NOT IN (1, 2, 3, NULL) PRINT '1 NOT IN (1, 2, 3, NULL)'
IF 1 NOT IN (2, 3, NULL) PRINT '1 NOT IN (2, 3, NULL)'
IF 1 NOT IN (2, 3) PRINT '1 NOT IN (2, 3)' -- Prints
IF 1 IN (1, 2, 3, NULL) PRINT '1 IN (1, 2, 3, NULL)' -- Prints
IF 1 IN (2, 3, NULL) PRINT '1 IN (2, 3, NULL)'
IF 1 IN (2, 3) PRINT '1 IN (2, 3)'