2
declare @test int
declare @test2 int = 3

select case when @test != @test2 then 1 else 0 end
select case when @test = @test2 then 1 else 0 end

The result is always 0. Can someone explain this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ledgeJumper
  • 3,560
  • 14
  • 45
  • 92

3 Answers3

4

You haven't set a value for @test yet, there for it is NULL.

@test will never be equal or not equal to @test2, since any comparison against NULL will be false.

LittleBobbyTables - Au Revoir
  • 32,008
  • 25
  • 109
  • 114
  • And NULL cannot be compared with =/!=, it will always be false. – Joe Oct 19 '12 at 17:56
  • 1
    @joe -- correct, I was trying to ninja edit that in as you commented :) – LittleBobbyTables - Au Revoir Oct 19 '12 at 17:57
  • Right, I know you have to say @test is null, or is not null. I am wondering why this is a behavior in sql. In my head Null is not the same as 3, so I would expect a true result. – ledgeJumper Oct 19 '12 at 17:59
  • nevermind, found the answer as to why it behaves this way! http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server – ledgeJumper Oct 19 '12 at 18:02
  • This can probably do a better job explaining in a brief space than I could: http://en.wikipedia.org/wiki/Null_(SQL). `NULL` isn't something, it's *unkonwn*. `3 <> 1` is true, but `3 <> NULL` is false, because NULL is unkonwn and cannot be evaluated. – LittleBobbyTables - Au Revoir Oct 19 '12 at 18:03
2

This behavior is controlled by the ANSI_NULLS set option. In fact if you SET ANSI_NULLS OFF you will get a 1 for the first select.

NULL is a special value, it is an unknown value. You can read your queries like this:

Select a value, when an unknown value is not equal to 3 Select a value, when an unknown value is equal to 3

Since the value is unknown it may or may not match, so you can't assume that it's not equal, as it is possible that they are equal. Since your comparing an unknown value it's like your comparing all possible values, and since there is one possible value that invalidates it the statement is false.

JoshBerke
  • 66,142
  • 25
  • 126
  • 164
  • Welcome! This was one of the weird things I had to wrap my head around once as well:-) It's like TrinaryLogic and unknown is false:-) – JoshBerke Oct 19 '12 at 19:53
0

If I am not mistaken when you declare a variable without a default value the value assigned is null. So in your two SELECT CASE statements you are comparing null to an actual value and the comparison will not work.

mreyeros
  • 4,359
  • 20
  • 24