SQL 查询问题:SELECT ... NOT IN [英] SQL query question: SELECT ... NOT IN

问题描述

我肯定犯了一个愚蠢的错误，但我不知道是什么:

I am sure making a silly mistake but I can't figure what:

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

In SQL Server 2005 I am trying select all customers except those who have made a reservation before 2 AM.

当我运行此查询时:

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"部分:

returns 152.000 results and the "NOT IN" part:

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

确保您的列表参数不包含空值.

Make sure your list parameter does not contain null values.

解释如下:

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

等同于:

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

• 最后一次比较的结果为 null.
• 该空值与布尔表达式的其余部分进行 OR 运算，产生空值.(*)
• null 被否定，产生 null.
• null 不为真 - where 子句只保留真行，因此所有行都被过滤.

(*) 这个解释很好，但我想解决一件事，以避免未来的吹毛求疵.(TRUE OR NULL) 将评估为 TRUE.例如，如果 field1 = 3，这是相关的.该 TRUE 值将被否定为 FALSE，该行将被过滤.

(*) this explanation is pretty good, but I wish to address one thing to stave off future nit-picking. (TRUE OR NULL) would evaluate to TRUE. This is relevant if field1 = 3, for example. That TRUE value would be negated to FALSE and the row would be filtered.

这篇关于SQL 查询问题:SELECT ... NOT IN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！