Oracle COALESCE() Function

Oracle COALESCE() is a built-in function that returns the first non-null value in a list of arguments.

Oracle COALESCE() Syntax

Here is the syntax for the Oracle COALESCE() function:

COALESCE(expr1[, expr2 ...])

Parameters

expr1[, expr2 ...]

Required. A list of arguments. You should provide at least two arguments.

Return Value

The Oracle COALESCE() function returns the first non-null value in the list of arguments.

If any of the arguments are NULL, COALESCE() returns NULL.

Oracle COALESCE() Examples

Here are some examples that demonstrate the usage of the Oracle COALESCE() function.

Basic Usage

SELECT
    COALESCE(null, 'B'),
    COALESCE('A', 'B')
FROM dual;

Output:

COALESCE(NULL,'B')    COALESCE('A','B')
_____________________ ____________________
B                     A

NULL Parameters

If all of the parameters are NULL, the COALESCE() function returns NULL.

SET NULL 'NULL';
SELECT
    COALESCE(NULL, NULL),
    COALESCE(NULL, NULL, NULL)
FROM dual;

Output:

COALESCE(NULL,NULL)    COALESCE(NULL,NULL,NULL)
______________________ ___________________________
NULL                   NULL

In this example, we use the statement SET NULL 'NULL'; to display the NULL values as the string 'NULL'.

Conclusion

Oracle COALESCE() is a built-in function that returns the first non-null value in a list of arguments.