Using NOT predicates in SQL

Question

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

Answer 1

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