Last Updated: February 25, 2016

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

#sql

#database

Related protips

Closure Tables for Browsing Trees in SQL

76.06K

Complex database migrations with PHP/MySQL using Phinx

18.78K

MySQL Query Optimization

2.718K

2 Responses

Add your response

bchristie

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 ·

colinross

+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 ·