A comparison is performed to check whether an expression contains the NULL value.
operand IS [NOT] NULL
operand ::= expression
operand
Operand for the comparison. The value of operand must either be atomic or the name of a multiple column. If the operand is a multiple column, the entry for the column cannot be an external reference (i.e. the column of a superordinate query expression).
Result
operand is an atomic value:
Without NOT:
True if the value of operand is the NULL value.
False in all other cases.
With NOT:
True if the value in operand is not the NULL value.
False in all other cases.
operand is a multiple column:
Without NOT:
True if at least one occurrence the multiple column is the NULL value.
False in all other cases.
With NOT:
True if at least one occurrence of the multiple column is not the NULL value.
False in all other cases.
Examples
language1 IS NOT NULL
In the example, LANGUAGE1 is a single column. If LANGUAGE1 does not contain the null value, the comparison is true. The comparison NOT language1 IS NULL would also return the same truth value.
LANGUAGE2(1..5) is a multiple column containing the null value in some, but not all of the columns. The comparison language2(1..5) IS NOT NULL returns true in this case and NOT (language(1..5) IS NULL) returns the truth value false.
column IS NOT NULL and NOT (column IS NULL) are thus not equivalent if column is a multiple column. This becomes clear if language2(1..5) IS NOT NULL is represented as:
language2(1) IS NOT NULL OR language2(2) IS NOT NULL OR ...
language2(5) IS NOT NULL
The comparison returns true if at least one occurrence of LANGUAGE2 is non-null.
NOT (language(1..5) IS NULL) on the other hand, can be represented as:
NOT (language(1) IS NULL OR language(2) IS NULL ... OR language(5) IS
NULL)
This comparison returns true if the comparisons with the null value in the parentheses following NOT return false. i.e. if all the occurrences of LANGUAGE2 are non-NULL.
Select the orders from the ORDERS table that have not yet been dealt with completely, i.e. for which the actual date is the NULL value.
SELECT order_num, order_text, target FROM orders WHERE actual IS NULL order_num order_text target 250 Mailmerge intro <date> 251 Customer administration <date> 300 Network test/comparison 305 Staff training <date>