Anomaly with NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Anomaly with NULL

Author

Topic

yilpiz
Starting Member

1 Post

Posted - 2014-12-26 : 03:48:06

Hello All,

Can someone explain why these two SQL codes return same result 0?

select CASE WHEN NULL IN ('123') THEN 1 ELSE 0 END AS Test
select CASE WHEN NOT NULL IN ('123') THEN 1 ELSE 0 END AS Test

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-12-26 : 07:01:07

using NULL in IN clause doesn't make sense..
try like this..


SELECT CASE WHEN  ('123') IS NULL then 1 else 0 END 
SELECT CASE WHEN ('123') IS NOT NULL then 1 else 0 END

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-12-26 : 07:55:17

The first query: null is not equal to '123', ergo result is 0
The second query: "not null" could be any value but null, so it could be '123', but then again it couldn't. It most likely isn't, ergo result is 0.

You could try this, if you want the result to be 1:
select CASE WHEN NULL NOT IN ('123') THEN 1 ELSE 0 END AS Test

Now, none of these queries makes sence when we don't know what you are trying to do, and I might suggest you just rewrite them to:
select 0 AS Test
select 1 AS Test

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources