When is "NOT" not a negation?

Question

Why do both of the following return zero? Surely the second is a negation of the first? I am using SQL Server 2008.

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 @a = @b
                  ) THEN 1
         ELSE 0
    END -- Also returns 0

Answer 1

NOT is always a negation. The reason for this behaviour of T-SQL lies in the fact that null values are treated in a special way depending on a database configuration setting (known as ansi_nulls). Depending on this setting, null is either treated in the same way as any other value or it is treated as "value not set". In this case, all expressions containing null values are regarded as invalid.

Furthermore, the expression

(@a IS NULL AND @b IS NULL)
OR 
@a = @b

covers only the case when both variables are NULL, it does not deal with cases when either @a or @b is NULL. If that happens, the result depends on the setting of ansi_nulls: if it is on, then the result of @a = @b is always false if one of the variables is NULL.

If ansi_nulls is off, then NULL is treated as a value and behaves as you expect.

To avoid such unexpected behaviour, you should cover all cases as follows:

DECLARE 
    @a VARCHAR(10) = 'a',
    @b VARCHAR(10) = null

SELECT  
    CASE 
        WHEN (@a IS NOT null AND @b IS null) THEN 0
        WHEN (@a IS null AND @b IS NOT null) THEN 0
        WHEN (@a IS null AND @b IS null) THEN 1
        WHEN (@a=@b) THEN 1
    ELSE 0
    END 

Note that in this example all null cases are dealt with before the @a=@b case is checked (in a CASE statement, the WHEN's are processed in the order as they appear, and if a condition matches, processing is finished and the specified value is returned).

---

To test all possible (relevant) combinations, you can use this script:

DECLARE @combinations TABLE (
    a VARCHAR(10),b VARCHAR(10)
    )

INSERT INTO @combinations
    SELECT 'a', null 
    UNION SELECT null, 'b'
    UNION SELECT 'a', 'b'
    UNION SELECT null, null
    UNION SELECT 'a', 'a'

SELECT a, b,
    CASE 
        WHEN (a IS NOT null AND b IS null) THEN 0
        WHEN (a IS null AND b IS NOT null) THEN 0
        WHEN (a IS null AND b IS null) THEN 1
        WHEN (a=b) THEN 1
    ELSE 0
    END as result
from @combinations
order by result 

It returns:

In other words, in this script null is treated as a value, hence a='a' and b=null returns 0, which is what you've expected. Only if both variables are equal (or both null), it returns 1.

Answer 2

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.

Answer 3

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 !

Answer 4

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