i0eg1a
Last Updated: February 25, 2016
·
293
· mrighelli

Stay away from NULL

In SqlServer NULL is not equal to NULL… try this at home

DECLARE @Value1 INT, @Value2 INT
SET @Value1 = NULL
SET @Value2 = NULL

IF (@Value1 = @Value2)
    PRINT 'EQUAL VALUES'
ELSE
    PRINT 'NOT EQUAL'

IF NOT(@Value1 = @Value2)
    PRINT 'EQUAL VALUES'
ELSE
    PRINT 'NOT EQUAL'

output: NOT EQUAL

2 Responses
Add your response

3829

That's because most DBMSs require that you check @var IS NULL and [conversely] @var IS NOT NULL. NULL isn't what you think it is in a database; it's more of a flag than a value.

This isn't anything new, just be aware you should always use IS or IS NOT when comparing a NULL.

p.s. MySQL (of reference) has explicit documentation for is null & is not null. MSDN does something similar with a whole page on null comparison.

over 1 year ago ·
3842

+1 to this not being anything new. NULL is not a value, it is the absence of it-- a placeholder to represent that no value exists. If no value exists, surely it can't be equal to anything, including another NULL.

over 1 year ago ·