Not Statement not working as expected with NULL values

  • as400

    SSC Enthusiast

    Points: 188

    The first three columns come back as expected.  All I tried to do is wrap the third in a not statement and I would have thought it would have returned the opposite as the third.

    Change any of the variables to NULL and it doesn't work.

    Can someone explain why this works this way.

    Thanks

     

    declare

    @x int=1,

    @y int=2,

    @tempX int=1,

    @tempY int=2

    select

    case when @x=@tempX or (@x is null and @tempX is null) then 1 else 0 end,

    case when @y=@tempY or (@y is null and @tempY is null) then 1 else 0 end,

    case when (@x=@tempX or (@x is null and @tempX is null)) and (@y=@tempY or (@y is null and @tempY is null)) then 1 else 0 end,

    case when not ((@x=@tempX or (@x is null and @tempX is null)) and (@y=@tempY or (@y is null and @tempY is null))) then 1 else 0 end

  • Mr. Brian Gale

    SSC-Insane

    Points: 23169

    That is a fun one with NULLs.  The problem is that @x=@tempX can be true or false when either of those is NULL.  Doing a comparison on if @x=NULL or a comparison on not(@x=NULL) will give you false to both.  It is because NULL is an unknown value.

    Try this query:

    DECLARE
    @x INT = 1

    SELECT
    CASE
    WHEN @x = NULL
    THEN 1
    ELSE 0
    END
    , CASE
    WHEN NOT (@x = NULL)
    THEN 1
    ELSE 0
    END;

    and you will see it is "0" for both columns.  This is because NULL is an unknown value.  SQL is seeing NULL as any possible value.  With the first query, there is a value for NULL where @x will not equal NULL and that is any value other than 1.  In the second query, there is a value for NULL such that @x will equal NULL and that is the value 1.  Since NULL is an unknown, doing a comparison on NULL, SQL will give you a consistent result as you have seen.

    A better approach is to use ISNULL and give the variable a value.  Fun thing, if you set @x to NULL, you still get 0 in both columns.  this is because NULL does not equal NULL, but also could equal NULL.

    This is one of the many reasons why NULL is bad to have in a table unless "unknown" is a valid value.  NULL, in SQL Server, should be treated as an unknown value that may or may not equal what you are comparing.

  • as400

    SSC Enthusiast

    Points: 188

    I understand that null cannot equal null.   If you look at my query that is why there is an or statement of (@x is null and @tempX is null).

    Each of the statements brings back what is expect just not when you perform the NOT of it.

  • Mr. Brian Gale

    SSC-Insane

    Points: 23169

    The issue is that NULL is an unknown value.  @x=NULL and NOT(@x=NULL) both give a result of FALSE.  Technically, they give NULL which gets evaluated to FALSE in a CASE statement as it is not TRUE.  This is because when you compare something to NULL, the result is NULL.  So your statement of "not(@x=@tempX OR (@x is null and @tempX is NULL))" evaluates out to "not(NULL OR <doesn't matter anymore>)" and then down to NOT(NULL) which is false.

    CASE statements can evaluate to TRUE, FALSE, or NULL.  TRUE will follow your "THEN" logic, FALSE and NULL will follow the ELSE logic.

    It would be like if I asked you "do I wear glasses?"  and then I asked "do I not wear glasses?".  The answer to both questions is the same from you - I don't know aka unknown aka NULL.

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

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