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

SQL query question: SELECT ... NOT IN

提问by Santiago Corredtheitroada

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

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

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

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

When I run this query:

当我运行此查询时：

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

I get 0 results.

我得到 0 个结果。

But

但

SELECT idCustomer FROM reservations 

returns 152.000 results and the "NOT IN" part:

返回 152.000 个结果和“NOT IN”部分：

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

returns only 284 rows

仅返回 284 行

回答by Amy B

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.

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

Here's an explanation:

这是一个解释：

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

is the same as:

是相同的：

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

• That last comparision evaluates to null.
• That null is OR'd with the rest of the boolean expression, yielding null. (*)
• null is negated, yielding null.
• null is not true - the where clause only keeps true rows, so all rows are filtered.

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

(*) Edit: 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.

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

回答by Cade Roux

It's always dangerous to have NULLin the INlist - it often behaves as expected for the INbut not for the NOT IN:

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

回答by jerryhung

Given it's SQL 2005, you can also try this It's similar to Oracle's MINUS command (opposite of UNION)

鉴于它是SQL 2005，你也可以试试这个它类似于Oracle的MINUS命令（与UNION相反）

But I would also suggest adding the DATEPART ( hour, insertDate) column for debug

但我也建议添加 DATEPART (hour, insertDate) 列进行调试

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

回答by Kevin Fairchild

SELECT Reservations.idCustomer FROM Reservations (nolock)
LEFT OUTER JOIN @reservations ExcludedReservations (nolock) ON Reservations.idCustomer=ExcludedReservations.idCustomer AND DATEPART(hour, ExcludedReservations.insertDate) < 2
WHERE ExcludedReservations.idCustomer IS NULL AND Reservations.idCustomer IS NOT NULL
GROUP BY Reservations.idCustomer

[Update: Added additional criteria to handle idCustomer being NULL, which was apparently the main issue the original poster had]

[更新：添加了额外的标准来处理 idCustomer 为 NULL，这显然是原始海报的主要问题]

回答by Kevin Fairchild

Sorry if I've missed the point, but wouldn't the following do what you want on it's own?

抱歉，如果我没有抓住重点，但是以下内容不会自己做您想做的事吗？

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

回答by Akash Pawar

SELECT MIN(A.maxsal) secondhigh
FROM (
      SELECT TOP 2 MAX(EmployeeBasic) maxsal
      FROM M_Salary
      GROUP BY EmployeeBasic
      ORDER BY EmployeeBasic DESC
     ) A

回答by santhosh

select * from table_name where id=5 and column_name not in ('sandy,'pandy');