How null values works
(Kristofer Gafvert, October 14, 2009)A NULL value means that the value is unknown. It does not mean that the value is empty or zero. This also means that two null values are the same and a comparison between two null values returns UNKNOWN (neither true nor false). So "age = null" would return neither TRUE nor FALSE if age would be null.
Remember the following about null values:
- IN SQL Server Management Studio, null values are shown with the text "NULL". This does not mean that the text "NULL" is equal to NULL.
- To test if a value is NULL, use the operators IS NULL or IS NOT NULL in the WHERE clause.
- Insert NULL values by explicitly stating NULL in the INSERT/UPDATE statement. You may also leave a column out in an INSERT statement, but it is more readable by explicitly stating NULL.
- NULL values cannot be used in a column that is used as a primary key.
- Exclude NULL values when you do calculations, or transform it into a value. Depending on the kind of calculation (for example an average), the result may become inaccurate.
- Logical and comparison operators may return a third result of UNKNOWN (instead of just TRUE or FALSE) when NULL values are compared incorrectly. If this UNKNOWN is compared in an AND comparison, the result is always UNKNOWN unless the other operand is FALSE. In an OR comparison, the result it always UNKNOWN unless the other operand is TRUE.
The behavior of NULL can be changed by setting ANSI_NULLS to OFF. When ANSI_NULLS is OFF, the statement "age = null" would be TRUE instead of UNKNOWN if age would by null.
Remember though that ANSI_NULLS ON only affects a comparison if one of the operands is either a variable that is NULL or a literal NULL. If both operands are columns or compound expressions, the setting does not affect the comparison and you need to use IS [NOT] NULL.
The conclusion is, based on this article, that you should avoid using NULL if you can. It is the source of many errors, unless correctly treated.
Applies to [?]
SQL Server 2008
See also