null. The examples provided expose various cases where this error arises, together with some recommendations on how to mitigate the error.
NULL in SQL, as well as programming in general, means literally "nothing". In SQL, it is easier to understand as "the absence of any value".
It is important to distinguish it from seemingly empty values, such as the empty string
'' or the number
0, neither of which are actually
It is also important to be careful not to enclose
NULL in quotes, like
'NULL', which is allowed in columns that accept text, but is not
NULL and can cause errors and incorrect data sets.
In SQL Server,
NULL represents data that is missing, or unknown. This means that
NULL is not really a value; it's better described as a placeholder for a value. This is also the reason why you can't compare
NULL with any value, and not even with another
a = NULLas the result would be UNKNOWN. Instead use
a IS NULLor
a IS NOT NULLconditions. NULL is not equal to NULL. To compare two expressions where null can happen, use one of the functions described below. All operators except concatenation return NULL if one of their operand is NULL. For instance the result of
3 * NULL + 5is null.
null coalescing operator (
??) has been added as syntactic sugar for the common case of needing to use a ternary in conjunction with
It returns its first operand if it exists and is not
NULL; otherwise it returns its second operand.