September 16, 2020 at 3:15 pm
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
September 16, 2020 at 4:40 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 16, 2020 at 5:26 pm
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.
September 16, 2020 at 5:54 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply