Parameters

expr1 (mandatory) – This is the first expression to check for a NULL value. If it is not NULL, then this value is returned.

expr2 (mandatory) – This is the second expression to check for a NULL value.

expr… (optional) – Further expressions can be specified, to be checked in case expr1 and expr2 are NULL.

Some things to note for the Oracle COALESCE function:

Only two expressions are needed.

The first non-NULL expression is returned by the function. This could be expr1, expr2, expr5, or any other expression.

If all specified expressions are NULL, the function returns NULL. So, it is not a guarantee that no NULL values will be returned.

The data type returned will be calculated by Oracle based on the expressions.

This function also uses what’s called “short circuit evaluation”. According to Oracle, it means that the expressions are evaluated and determined if they are NULL one at a time, rather than all values being evaluated before checking if any are NULL.

So, if you have a long list of expressions with functions and are worried about the run time for calculating each of the functions for each row, you won’t need to be as worried, because the functions will only calculate if the expressions before them are NULL.

Can You Use Oracle COALESCE With a Date?

Yes, you can use a DATE value inside COALESCE. It’s treated the same as supplying any other type of expression.

See the examples below for more information on using a date.

Can You Use Oracle COALESCE With an Empty String?

When using COALESCE, an empty string is treated the same as a NULL value.

So, as you can see in the examples below, the COALESCE statement with an empty string tries to use the next available value.

Can You Use Oracle COALESCE in a WHERE Clause?

Yes, you can, though it may not be the best way to do it.

The example below shows how to use a COALESCE function in a WHERE clause. However, there are probably better ways to write the statement, depending on how you use it in your application.