Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

COALESCE (SQL-92 compatible)

Syntax

COALESCE ( expression1, expression2 [ , ... ] )

Description

The COALESCE scalar function is a type of conditional expression. (See Conditional Expressions for more information and a summary of all the conditional expressions.)

COALESCE specifies a series of expressions, and returns the first expression whose value is not null. If all the expressions evaluate as null, COALESCE returns a null value.

The COALESCE syntax is shorthand notation for a common case that can also be represented in a CASE expression. The following two formulations are equivalent:

COALESCE ( expression1 , expression2 , expression3 )

CASE

WHEN expression1 IS NOT NULL THEN expression1

WHEN expression2 IS NOT NULL THEN expression2

ELSE expression3

Example

SELECT COALESCE(end_date, start_date) from job_hist;

Notes

  • This function is not allowed in a GROUP BY clause
  • Arguments to this function cannot be query expressions

TOCIndex