What is the difference between these two statements?
SELECT * FROM OrderLog WHERE AssetNum = NULL
SELECT * FROM OrderLog WHERE AssetNum IS NULL
What is the difference between these two statements?
SELECT * FROM OrderLog WHERE AssetNum = NULL
SELECT * FROM OrderLog WHERE AssetNum IS NULL
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.
"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.