<p>I am sure making a silly mistake but I can't figure what:</p>

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

<p>When I run this query:</p>

<pre><code>SELECT idCustomer FROM reservations 
WHERE idCustomer NOT IN 
  (SELECT distinct idCustomer FROM reservations 
   WHERE DATEPART ( hour, insertDate) &lt; 2)
</code></pre>

<p>I get 0 results.</p>

<p>But </p>

<pre><code>SELECT idCustomer FROM reservations 
</code></pre>

<p>returns 152.000 results and the "NOT IN" part:</p>

<pre><code>SELECT distinct idCustomer FROM reservations 
WHERE DATEPART ( hour, insertDate) &lt; 2
</code></pre>

<p>returns only 284 rows</p>

```
```
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.