Properties of NULL

The following list expands on the properties of a NULL value.

  • The difference between FALSE and UNKNOWN   Although neither FALSE nor UNKNOWN returns values, there is an important logical difference between FALSE and UNKNOWN; the opposite of false ("not false") is true, whereas the opposite of UNKNOWN does not mean something is known. For example, 1 = 2 evaluates to false, and 1 != 2 (1 does not equal 2) evaluates to true.

    But if a NULL is included in a comparison, you cannot negate the expression to get the opposite set of rows or the opposite truth value. An UNKNOWN value remains UNKNOWN.

  • Substituting a value for NULL values   You can use the ISNULL built-in function to substitute a particular value for NULL values. The substitution is made only for display purposes; actual column values are not affected. The syntax is:

    ISNULL( expression, value  )

    For example, use the following statement to select all the rows from Departments, and display all the NULL values in column DepartmentHeadID with the value -1.

    SELECT DepartmentID, 
           DepartmentName, 
           ISNULL( DepartmentHeadID, -1 ) AS DepartmentHead
       FROM Departments;

  • Expressions that evaluate to NULL   An expression with an arithmetic or bitwise operator evaluates to NULL if any of the operands are the NULL value. For example, 1 + column1 evaluates to NULL if column1 is NULL. See Arithmetic operators, and Bitwise operators.

  • Concatenating strings and NULL   If you concatenate a string and NULL, the expression evaluates to the string. For example, the following statement returns the string abcdef:
    SELECT 'abc' || NULL || 'def';