“Unknown” Values: NULL

A NULL value in a column means no entry has been made in that column. A data value for the column is “unknown” or “not available.”

NULL is not synonymous with “zero” or “blank.” Rather, null values allow you to distinguish between a deliberate entry of zero for numeric columns (or blank for character columns) and a non-entry, which is NULL for both numeric and character columns.

If you type the word ‘‘NULL’’ in a character column and include quotation marks, it is treated as data, rather than a null value.

Query results display the word NULL. For example, the advance column of the titles table allows null values. By inspecting the data in that column, you can tell whether a book had no advance payment by agreement (the row MC2222 has zero in the advance column) or whether the advance amount was not known when the data was entered (the row MC3026 has NULL in the advance column).

select title_id, type, advance 
from titles 
where pub_id = "0877" 

title_id     type             advance 
--------     ----------       --------- 
MC2222       mod_cook              0.00 
MC3021       mod_cook         15,000.00 
MC3026       UNDECIDED             NULL 
PS1372       psychology        7,000.00 
TC3218       trad_cook         7,000.00 
TC4203       trad_cook         4,000.00 
TC7777       trad_cook         8,000.00 
 
(7 rows affected)  

• SQL Standard for NULL Concatenation
  Use set sqlnull on to implement SQL standard for NULL concatenation.
• Test a Column for Null Values
  Use is null in where, if, and while clauses to compare column values to NULL, and to select them or perform a particular action based on the results of the comparison.
• Difference Between False and Unknown
  There is an important logical difference between false and unknown: the opposite of false (“not false”) is true, while the opposite of unknown is still unknown.
• Substitute a Value for NULLs
  Use the isnull built-in function to substitute a particular value for nulls. The substitution is made only for display purposes; actual column values are not affected.
• Expressions that Evaluate to NULL
  An expression with an arithmetic or bitwise operator evaluates to NULL if any of the operands is null.
• Concatenate Strings and NULL
  If you concatenate a string and NULL, the expression evaluates to the string.
• System-Generated NULLs
  In Transact-SQL, system-generated NULLs, such as those that result from a system function like convert, behave differently than user-assigned NULLs.