sql server - Confusing null compares combined with NOT

Question

I realize that comparing NULL to any other value (including NULL) will always result in false.

DECLARE @IsSet bit = NULL;

SELECT IIF(@IsSet = 1, 'true', 'false')
SELECT IIF(@IsSet != 1, 'true', 'false')

This outputs:

false
false

But this is part that confuses me:

SELECT IIF(NOT(@IsSet = 1), 'true', 'false')
SELECT IIF(NOT(@IsSet != 1), 'true', 'false')

This also outputs:

false
false

I would expect that the NOT would have flipped the value to TRUE. (Which it does if @IsSet is set to 0 for the first expression)

It seems that the compare to the null value has some power over the boolean logic outside the parenthesis.

But the null compare is not all powerful over boolean logic:

SELECT IIF((@IsSet = 1) OR (1=1), 'true', 'false')
SELECT IIF((@IsSet != 1) OR (1=1), 'true', 'false')

This returns:

true
true

I don't understand what is happening here, but I assume that this is done on purpose. But I don't know why.

Can someone explain why NOT(NULL!=1) does not equal true.

Answer 1

A comparison with NULL results in UNKNOWN rather than TRUE or FALSE. NOT UNKNOWN also results in UNKNOWN, which is neither TRUE nor FALSE. One cannot "flip" UNKNOWN to a Boolean value using NOT.

This 3-way logic requires one to use IS NULL or IS NOT NULL to test for NULL values rather than traditional Boolean logic.