Description of the illustration coalesce.gif

`COALESCE`

returns the first non-null `expr`

in the expression list. At least one `expr`

must not be the literal `NULL`

. If all occurrences of `expr`

evaluate to null, then the function returns null.

Oracle Database uses short-circuit evaluation. That is, 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.

See Also:

Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and "Numeric Precedence" for information on numeric precedenceThis 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
```

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; PRODUCT_ID LIST_PRICE MIN_PRICE Sale ---------- ---------- ---------- ---------- 2382 850 731 765 3355 5 1770 73 73 2378 305 247 274.5 1769 48 43.2