15.00 - NULLIF Expression - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)
Last Update

NULLIF Expression


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:

    WHEN scalar_expression_1=scalar_expression_2 
    ELSE scalar_expression_1 




Syntax element …

Specifies …


the scalar expression to the left of the = in the expanded CASE expression, as shown previously in “Purpose.”


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



A BLOB can only appear in the argument list when it is cast to BYTE or VARBYTE.


A CLOB can only appear in the argument list when it is cast to CHAR or VARCHAR.


Multiple UDTs can appear in the argument list only when they are identical types and have an ordering definition.


The following examples show queries on the following table:

   CREATE TABLE Membership
      (FullName CHARACTER(39)
      ,Age SMALLINT
      ,Code CHARACTER(4) );


Here is the ANSI-compliant form of the Teradata SQL NULLIFZERO(Age) function, and is more versatile.

   SELECT FullName, NULLIF (Age,0) FROM Membership;


In the following query, blanks indicate no value.

   SELECT FullName, NULLIF (Code, '    ') FROM Membership;


The following example uses NULLIF in an expression with an arithmetic operator.

   SELECT NULLIF(Age,0) * 100;