When is "NOT" not a negation?

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 | FT | T | T | TU | T | U | UF | 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.

This 'weird' behavior that you are encountering is caused by the NULL values.

The negation of NOT (Something that returns NULL) is not TRUE , it's still NULL .

E.G.

SELECT * FROM <Table> WHERE <Column> = null -- 0 rows SELECT * FROM <Table> WHERE NOT (<Column> = null) -- Still 0 rows

In addition to what been said here, you can avoid that behavior by using

SET ANSI_NULLS OFF

Which will let the optimizer to treat NULL as normal value, and return TRUE\FALSE . You should note that this is not recommended at all and you should avoid it !

It is problem with @a=@b if either of this value is null then it will be problem

If you try below code will give correct results

DECLARE     @a VARCHAR(10) = NULL ,    @b VARCHAR(10) = 'a'SELECT      CASE WHEN ( ( @a IS NULL                      AND @b IS NULL                    )                    OR @a = @b                  ) THEN 1         ELSE 0    END , -- returns 0    CASE WHEN NOT ( ( @a IS NULL                      AND @b IS NULL                    )                    OR ISNULL(@a,-1) = ISNULL(@b,-1)                  ) THEN 1         ELSE 0    END -- also returns 0