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
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';
Discuss this page in DocCommentXchange.
|Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0|