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

BradC · Accepted Answer · 2010-11-18 19:01:58Z

5

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

edited Nov 18, 2010 at 19:01

answered Nov 18, 2010 at 14:08

BradC

40.3k14 gold badges77 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

onedaywhen Over a year ago

NULL = 5 evaluates to UNKNOWN (rather than "no").

BradC Over a year ago

I was being a bit metaphorical. The point is that a row where ValueB is NULL will be excluded from both.

BradC Over a year ago

@onedaywhen - Edited my post to be slightly more precise based on your comment.

JNK · Accepted Answer · 2010-11-18 14:08:41Z

2

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.

answered Nov 18, 2010 at 14:08

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Collectives™ on Stack Overflow

Using NOT predicates in SQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related