It is a negation. However, you need to understand ANSI NULLs – a negation of a NULL is also a NULL. And NULL is a falsy truth value.
Therefore, if any of your arguments is null, the result of @a = @b
will be null (falsy), and a negation of that will also be a null (falsy).
To use negation the way you want, you need to get rid of the NULL. However, it might be easier to simply reverse the results of the comparison instead:
case when (...) then 1 else 0 end,
case when (...) then 0 else 1 end
Which will always give you either 1, 0
or 0, 1
.
EDIT:
As jpmc26 noted, it might be useful to expand a bit on how nulls behave so that you don’t get the idea that a single NULL
will make everything NULL
. There are operators which do not always return null
when one of their arguments is null – the most obvious example being is null
, of course.
In a more broad example, logical operators in T-SQL use Kleene’s algebra (or something similar), which defines the truth values of an OR
expression like so:
| T | U | F
T | T | T | T
U | T | U | U
F | T | U | F
(AND
is analogous, as are the other operators)
So you can see that if at least one of the arguments is true, the result will also be true, even if the other is an unknown (“null”). Which also means that not(T or U)
will give you a falsy truth value, while not(F or U)
will also give you a falsy truth value, despite F or U
being falsy – since F or U
is U
, and not(U)
is also U
, which is falsy.
This is important to explain why your expression works the way you expect it to when both arguments are null – the @a is null and @b is null
evaluates to true, and true or unknown
evaluates to true
.