NOT Operator and NULLs

Question

NOT Operator and NULLs

Default port

Points: 1449

November 20, 2019 at 12:28 pm

--test data
declare @T table(a int null, b int null)

insert @T(a, b)
select 0, 0 union all
select 1, 0 union all
select 0, 1 union all
select 1, 1 union all
select null, null union all
select 0, null union all
select null, 0 union all
select 1, null union all
select null, 1 

select * from @T

--1. filter for positive case, result as expected
select *
from @T
where
a = 1 or b = 1

--2. filter for positive case, result the same as above
select *
from @T
where
isnull(a, 9999) = 1 or isnull(b, 9999) = 1

--3. filter for negative case, result NOT as expected
select *
from @T
where
not (a = 1 or b = 1)

--4. filter for negative case, result as expected
select *
from @T
where
not (isnull(a, 9999) = 1 or isnull(b, 9999) = 1)

What is happening in 3. above? I'm thinking it's because a row that the OR evaluates to UNKNOWN is then NOT'd, and NOT(UNKNOWN) is UNKNOWN - the UNKNOWN is not negated?

Just come across this with some real data, and slipped up because 1. and 2. are equivalent, and the problem only arises when using the NOT.

Thanks in advance for any advice.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

rVadim Hall of Fame Points: 3969 More actions · Answer 1

not (a = 1 or b = 1) is equivalent to a <> 1 and b <> 1 which is only True for the row where both a and b are zeros (in your example data set).

--Vadim R.

kuopaz Default port Points: 1449 More actions · Answer 2

Yes, when you re-arrange to that - remove the NOT - the result in 3. makes sense.

I find it difficult to see it with the original: not (a = 1 or b = 1).

rVadim Hall of Fame Points: 3969 More actions · Answer 3

I hear you, it's tempting to think that it will negate 5 rows selected by a = 1 or b = 1 and return the other 4. But in fact, SQL Server converts it to a <> 1 and b <> 1. If you enable actual execution plan in SSMS, you can see that.

--Vadim R.

kuopaz Default port Points: 1449 More actions · Answer 4

AND and OR treat UNKNOWN as FALSE, and the result is what I'd expect from the WHERE clause. But this is not the case with NOT and UNKNOWN. To avoid an unexpected result (at least to me) when you have a NOT, the NULLs must be handled, such as in 4. above.

Might be laboring the point, but I fear I've made this mistake more than once and not noticed!