Using NOT predicates in SQL

Question

I have created several queries and cannot understand why the results are not as I expected.

I do not understand why Query II and III do not return the same results. I expect query II will return all rows not selected by Query I.

I would expect Query II and III to give the same results. In my opinion, results III are correct.

I’m sure I missed something, I just don’t know what.

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

Inquiries

All entries: select * from TestTable

I am. Custom query:

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

Result:

TestTableId ValueA ValueB
1           10   5
2           20   5

II. Same query but 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 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

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

, NULL , .

, NULL:

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

Answer 2

NOT, NULL .

A NULL - . SQL , NOT a 12, , a. 12.

:

. 2 12 , . , "". , " ", 2 "". SQL, 10 NULL.