15.00 - NULLIF Expression - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

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;