SQL query question: SELECT ... NOT IN

Question

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.

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.

But

SELECT idCustomer FROM reservations  

returns 152.000 results and the "NOT IN" part:

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

returns only 284 rows

Answer 1

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.

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

Answer 2

It's always dangerous to have NULL in the IN list - it often behaves as expected for the IN but not for the 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)'