SQL Difference Between IS NULL and =NULL

Understanding the difference between “IS NULL” and “= NULL”

When a variable is created in SQL with the declare statement it is created with no data and stored in the variable table (vtable) inside SQLs memory space. The vtable contains the name and memory address of the variable. However, when the variable is created no memory address is allocated to the variable and thus the variable is not defined in terms of memory.

When you SET the variable it is allotted a memory address and the initial data is stored in that address. When you SET the value again the data in the memory address pointed to by the variable is then changed to the new value.

Now for the difference and why each behaves the way it does.

“= NULL”

“= NULL” is an expression of value. Meaning, if the variable has been set and memory created for the storage of data it has a value. A variable can in fact be set to NULL which means the data value of the objects is unknown. If the value has been set like so:

DECLARE @val CHAR(4)
SET @val = NULL

You have explicitly set the value of the data to unknown and so when you do:

If @val = NULL

It will evaluate as a true expression.

But if I do:

DECLARE @val CHAR(4)
If @val =  NULL

It will evaluate to false.

The reason for this is the fact that I am checking for NULL as the value of @val. Since I have not SET the value of @val no memory address has been assigned and therefore no value exists for @val.

“IS NULL”

Now “IS NULL” is a little trickier and is the preferred method for evaluating the condition of a variable being NULL. When you use the “IS NULL” clause, it checks both the address of the variable and the data within the variable as being unknown. So if I for example do:

DECLARE @val CHAR(4)
If @val IS NULL
            PRINT ‘TRUE’
ELSE
            PRINT ‘FALSE’

SET @val = NULL

If @val IS NULL
            PRINT ‘TRUE’
ELSE
            PRINT ‘FALSE’

Both outputs will be TRUE. The reason is in the first @val IS NULL I have only declared the variable and no address space for data has been set which “IS NULL” check for. And in the second the value has been explicitly set to NULL which “IS NULL” checks also.

Additional Interesting Articles

PHP Cookie And Authentication
Execute Files Remotely Using PSExec
C# MailMessage Example
C# DataSet v DataReader
Pearson Coefficient
t-SQL Cursor

©2008 AndrewKimball.com