SQLTeam.com | Weblogs | Forums

Difference between NULL and IS NULL?


#1

What is the difference between these two statements?

SELECT * FROM OrderLog WHERE AssetNum = NULL

SELECT * FROM OrderLog WHERE AssetNum IS NULL


#2

The correct and recommended usage is "AssetNum IS NULL".

NULL is never equal to anything, (including NULL), so AssetNum = NULL will always return the result of the comparison as unknown. In SQL Server, you can set ANSI_NULLS to OFF to make it interpret AssetNum = NULL as being equivalent to AssetNum IS NULL, but it is a deprecated feature (meaning, it will be removed in some future version of SQL Server).

See here.


#3

"AssetNum = NULL" is effectively meaningless, because NULL is an "unknown" value. Thus, NULL can never be "=" to anything, and the only possible result from that comparison is also NULL, that is, "unknown".

To look at one concrete value, "is 2 = NULL?", which is really "is 2 equal to an unknown value"? The only possible answer to that is "unknown", since the unknown value might be 2 or it might not.