ASA SQL User's Guide
Queries: Selecting Data from a Table
The WHERE clause: specifying rows
Unknown Values: NULL
The following list expands on the properties of NULL.
The difference between FALSE and UNKNOWN Although neither FALSE nor UNKNOWN returns values, there is an important logical difference between FALSE and UNKNOWN, because the opposite of false ("not false") is true. For example,
1 = 2
evaluates to false and its opposite,
1 != 2
evaluates to true. But "not unknown" is still unknown. If null values are included in a comparison, you cannot negate the expression to get the opposite set of rows or the opposite truth value.
Substituting 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. The syntax is:
ISNULL( expression, value )
For example, use the following statement to select all the rows from test, and display all the null values in column t1 with the value unknown.
SELECT ISNULL(t1, 'unknown') FROM test
Expressions that evaluate to NULL An expression with an arithmetic or bitwise operator evaluates to NULL if any of the operands are null. 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:
SELECT 'abc' || NULL || 'def'
returns the string abcdef.