iAnywhere.com - Properties of 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 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 null. For example, the following statement evaluates to NULL if column1 is NULL:
```
1 + column1
```
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';
```