Using NOT predicates in SQL

I've created some queries and can't understand why the results are not as I expected.

I don't understand why Query II and III don't return the same results. I would expect query II to return all rows not selected by Query I.

I would have expected Query II and III to give the same results. In my opinion the results of III are the right ones.

I'm sure I miss something, I just don't know what.

The example:

Table:

CREATE TABLE [dbo].[TestTable](
 [TestTableId] [int] NOT NULL,
    [ValueA] [int] NULL,
 [ValueB] [int] NULL
) ON [PRIMARY]

Data:

TestTableId ValueA ValueB
1        10      5
2        20      5
3        10      NULL
4        20        NULL
5        NULL      10
6        10        10
7        NULL      NULL

Queries:

All records: select * from TestTable

I. A select query:

select * from TestTable 
where (ValueA = 10 or ValueA = 20) AND ValueB = 5

Result:

TestTableId ValueA ValueB
1           10   5
2           20   5

II. The same query but as NOT

select * from TestTable 
where NOT ((ValueA = 10 or ValueA = 20) AND ValueB = 5)

Result:

TestTableId ValueA ValueB
5           NULL   10
6           10   NULL

III. The same开发者_如何学编程 query as the second (I would think)

select * from TestTable where TestTable.TestTableId not in 
    (select TestTableId from TestTable 
where (ValueA = 10 or ValueA = 20) AND ValueB = 5)

Result:

TestTableId ValueA ValueB
3           10   NULL
4           20   NULL
5           NULL   10
6           10   10
7           NULL   NULL

NULLs are funny creatures. They will answer "I don't know" to both of the following questions:

Are you 5?  (... WHERE ValueB = 5)

and

Are you Not 5? (... WHERE NOT ValueB = 5)

Which results in NULL values being excluded from both queries, as you found.

You have to ask the question in a way that explicitly accounts for the NULLs:

... WHERE (ValueB IS NULL OR NOT ValueB = 5) ...

When using NOT, NULL values are a special situation.

A NULL is an unknown value. SQL can't say if it's NOT a 12, but it can say if it is a 12.

A good example:

You are at a party. You know 2 of the 12 people's names in the room, both are named John. You can tell me who the "John"s are. You can't tell me who is "Not Jack" besides the 2 "John"s. For SQL, those other 10 people in the room have a name of NULL.