8.13.3 NULLIF
Compares target-data-1 to target-data-2 and return NULL if they are equal, or target-data-1 if they are not equal.
- Organization of this subsection
(1) Specification format
scalar-function-NULLIF ::= NULLIF(target-data-1,target-data-2) target-data-1 ::= value-expression target-data-2 ::= value-expression
(2) Explanation of specification format
- target-data-1, target-data-2:
-
Specifies the target data to be compared.
The following rules apply:
-
Specify target-data-1 and target-data-2 in the form of value expressions. For details about value expressions, see 7.20 Value expression.
-
In target-data-1 and target-data-2, specify data whose data types can be compared. For details about data types that can be compared, see (1) Data types that can be compared in 6.2.2 Data types that can be converted, assigned, and compared. However, note the following exceptions:
• DATE type data cannot be compared to character string data (even to the predefined input representation of a date).
• TIME type data cannot be compared to character string data (even to the predefined input representation of a time).
• TIMESTAMP type data cannot be compared to character string data (even to the predefined input representation of a time stamp).
-
You cannot specify dynamic parameters by themselves for both target-data-1 and target-data-2.
-
If you specify a dynamic parameter for either target-data-1 or target-data-2, the data type of the other one will be assumed to be the data type of the dynamic parameter.
-
(3) Rules
-
The data type and data length of the execution result are determined according to the rules described in 7.20.2 Data types of the results of value expressions.
-
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
-
If target-data-1 has a null value, the execution result will be a null value.
-
NULLIF(target-data-1,target-data-2) is equivalent to the following CASE expression.
CASE WHEN target-data-1 = target-data-2 THEN NULL ELSE target-data-1 END
(4) Example
- Example:
-
Compare the values of columns C1 and C2 in table T1.
SELECT NULLIF("C1","C2") FROM "T1"