Numeric null value vs value--comparison

Question

Im trying to compare 2 values -1 is null and other is numeric, but it somehow equals to "equal"

declare @value1 int;
declare @value2 int;
select @value1 = null
select @value2 = 1
if (@value1 <> @value2)
    print 'not equal'
else 
    print 'equal'

Result: Equal

Tim Schmelter · Accepted Answer · 2013-07-30 15:03:36Z

9

You cannot compare NULL values, neither with = nor <> since NULL means undefined.

Use IS if you want to know if a values is NULL, you can use:

IF ( ( @value1 IS NULL 
       AND @value2 IS NULL ) 
      OR (( @value1 IS NOT NULL 
            AND @value2 IS NOT NULL 
            AND @value1 = @value2 )) ) 
  PRINT 'equal' 
ELSE 
  PRINT 'not equal'

See: What if null if null is null null null is null?

edited Jul 30, 2013 at 15:03

answered Jul 30, 2013 at 14:56

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cadrell0 · Accepted Answer · 2013-07-30 14:55:57Z

2

NULL fails all comparisons therefore if(null <> @value) is false.

Per MSDN

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN.

answered Jul 30, 2013 at 14:55

cadrell0

17.4k5 gold badges55 silver badges70 bronze badges

Collectives™ on Stack Overflow

Numeric null value vs value--comparison

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related