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.

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';