15.00 - COALESCE 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

COALESCE Expression


Returns NULL if all its arguments evaluate to null. Otherwise, it returns the value of the first non-null argument in the scalar_expression list.

COALESCE is a shorthand expression for the following full CASE expression:

    WHEN scalar_expression_1 IS NOT NULL 
    THEN scalar_expression_1 
    WHEN scalar_expression_n IS NOT NULL 
    THEN scalar_expression_n 




Syntax element …

Specifies …


an argument list.

Each COALESCE function must have at least two operands.

ANSI Compliance

This is ANSI SQL:2011 compliant.

Usage Notes

A scalar_expression_n in the argument list may be evaluated twice: once as a search condition and again as a return value for that search condition.

Using a nondeterministic function, such as RANDOM, in a scalar_expression_n may have unexpected results, because if the first calculation of scalar_expression_n is not NULL, the second calculation of that scalar_expression_n, which is returned as the value of the COALESCE expression, might be NULL.

You can use a scalar subquery in a COALESCE 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 COALESCE expression appears as:

   <CASE expression>

Restrictions on the Data Types in a COALESCE Expression

The following restrictions apply to CLOB, BLOB, and UDT types in a COALESCE 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 because Teradata Database does not perform implicit type conversion on UDTs in a COALESCE expression.


The following example returns the home phone number of the named individual (if present), or office phone if HomePhone is null, or MessageService if present and both home and office phone values are null. Returns NULL if all three values are null.

   SELECT Name, COALESCE (HomePhone, OfficePhone, MessageService) 
   FROM PhoneDir;


The following example uses COALESCE with an arithmetic operator.

   SELECT COALESCE(Boxes,0) * 100
   FROM Shipments;


The following example uses COALESCE with a comparison operator.

   SELECT Name
   FROM Directory
   WHERE Organization <> COALESCE (Level1, Level2, Level3);