7.19.6 NULL predicate
The NULL predicate is used to search for null values. For details about null values, see 6.7 Null value.
- Organization of this subsection
(1) Specification format
NULL-predicate ::= value-expression IS [NOT] NULL
(2) Explanation of specification format
- value-expression:
-
Specifies the column to be evaluated by the NULL predicate. It is specified in the form of a value expression. For details about value expressions, see 7.20 Value expression.
If a dynamic parameter is specified by itself, the assumed data type of the dynamic parameter will be VARCHAR(32000).
- NOT:
-
If NOT is specified, rows that are not the null value will become the target of retrieval.
(3) Evaluation of the predicate
The NULL predicate is TRUE for rows in which the value of the specified value expression is a null value. If NOT is specified, it is TRUE for rows in which the value of the specified value expression is not a null value.
(4) Examples
- Example 1
-
This example retrieves the customer IDs (USERID) from the customer table (USERSLIST) where the name (NAME) is the null value.
SELECT "USERID" FROM "USERSLIST" WHERE "NAME" IS NULL
The underlined portion indicates the NULL predicate.
- Example 2
-
This example retrieves the customer IDs (USERID) from the customer table (USERSLIST) where the name (NAME) is not the null value.
SELECT "USERID" FROM "USERSLIST" WHERE "NAME" IS NOT NULL
The underlined portion indicates the NULL predicate.