What is the function of the COALESCE function in Oracle?

In Oracle, the COALESCE function is used to return the value of the first non-null expression from multiple expressions. Its purpose is to return the value of the first non-null expression when given a set of expressions.

The syntax of the COALESCE function is as follows:
COALESCE(expr1, expr2, …)

Among them, expr1, expr2, and so on are the expressions to be checked, which can be multiple. The function evaluates these expressions in order and returns the value of the first non-empty expression. If all expressions are empty, it returns NULL.

Here is an example:
SELECT COALESCE(NULL, ‘Hello’, NULL, ‘World’);

In the example mentioned above, the COALESCE function evaluates each expression in order until it finds the first non-null expression. In this case, it returns ‘Hello’ as the result.