How is the coalesce function used in Oracle?

The COALESCE function in Oracle is used to return the value of the first non-null expression in a set of expressions. The syntax for its use is as follows:

Combine the values of the expressions expr1, expr2, expr3, etc., and return the first non-null value.

expr1, expr2, expr3, etc. are the expressions to be compared, which can be columns, constants, or functions. The COALESCE function compares each expression from left to right and returns the value of the first non-null expression.

Here are some examples:

1. Retrieve the value of the first non-empty column.

Choose the first non-null value from col1, col2, and col3 in the table.

1. Retrieve the value of the first non-null constant or expression.

Return ‘Value1’ as the output from the dual table.

1. Return the first non-empty returned value from a function.

Choose one of the functions func1(), func2(), or func3() that returns a non-null value and select it from the dual table.

Please note that the COALESCE function in Oracle can also be achieved using the NVL function with the same syntax.

If expr1 is not null, then NVL will return expr1; otherwise, it will return expr2.

expr1 is the expression to be compared, and expr2 is the default value to be returned. If expr1 is empty, then the value of expr2 will be returned.