NULLIF Expression
Purpose
Returns NULL if its arguments are equal. Otherwise, it returns its first argument, scalar_expression_1.
NULLIF is a shorthand expression for the following full CASE expression:
CASE
WHEN scalar_expression_1=scalar_expression_2
THEN NULL
ELSE scalar_expression_1
END
Syntax
where:
|
Syntax element … |
Specifies … |
|
scalar_expression_1 |
the scalar expression to the left of the = in the expanded CASE expression, as shown previously in “Purpose.” |
|
scalar_expression_2 |
the scalar expression to the right of the = in the expanded CASE expression, as shown previously in “Purpose.” |
ANSI Compliance
This is ANSI SQL:2011 compliant.
Usage Notes
The scalar_expression_1 argument may be evaluated twice: once as part of the search condition (see the preceding expanded CASE expression) and again as a return value for the ELSE clause.
Using a nondeterministic function, such as RANDOM, may have unexpected results if the first calculation of scalar_expression_1 is not equal to scalar_expression_2, in which case the result of the CASE expression is the value of the second calculation of scalar_expression_1, which may be equal to scalar_expression_2.
You can use a scalar subquery in a NULLIF expression. However, if you use a non-scalar subquery (a subquery that returns more than one row), a runtime error is returned.
For additional information, such as the rules for evaluation and result data type, see “CASE” on page 559.
Default Title
The default title for a NULLIF expression appears as:
<CASE expression>
Restrictions on the Data Types in a NULLIF Expression
The following restrictions apply to CLOB, BLOB, and UDT types in a NULLIF expression:
|
Data Type |
Restrictions |
|
BLOB |
A BLOB can only appear in the argument list when it is cast to BYTE or VARBYTE. |
|
CLOB |
A CLOB can only appear in the argument list when it is cast to CHAR or VARCHAR. |
|
UDT |
Multiple UDTs can appear in the argument list only when they are identical types and have an ordering definition. |
Examples
The following examples show queries on the following table:
CREATE TABLE Membership
(FullName CHARACTER(39)
,Age SMALLINT
,Code CHARACTER(4) );
Example
Here is the ANSI-compliant form of the Teradata SQL NULLIFZERO(Age) function, and is more versatile.
SELECT FullName, NULLIF (Age,0) FROM Membership;
Example
In the following query, blanks indicate no value.
SELECT FullName, NULLIF (Code, ' ') FROM Membership;
Example
The following example uses NULLIF in an expression with an arithmetic operator.
SELECT NULLIF(Age,0) * 100;