COALESCE

Syntax

Purpose

COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.

Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

If all occurrences of expr are numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

This function is a generalization of the NVL function.

You can also use COALESCE as a variety of the CASE expression. For example,

COALESCE (expr1, expr2)

is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

Similarly,

COALESCE (expr1, expr2, ..., exprn), for n>=3

is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1 
 ELSE COALESCE (expr2, ..., exprn) END

Examples

The following example uses the sample oe.product_information table to organize a clearance sale of products. It gives a 10% discount to all products with a list price. If there is no list price, then the sale price is the minimum price. If there is no minimum price, then the sale price is "5":

SELECT product_id, list_price, min_price,
 COALESCE(0.9*list_price, min_price, 5) "Sale"
 FROM product_information
 WHERE supplier_id = 102050
 ORDER BY product_id, list_price, min_price, "Sale";
PRODUCT_ID LIST_PRICE MIN_PRICE Sale
---------- ---------- ---------- ----------
 1769 48 43.2
 1770 73 73
 2378 305 247 274.5
 2382 850 731 765
 3355 5