Oracle COALESCE() Function

The Oracle (PL/SQL) COALESCE() function returns the first non-null expression in the list.

Syntax

COALESCE(expression1, expression2, ... expression_n)

Parameters

Return Value

Returns the first non-null expression from the given list. The function returns any datatype such as a string, numeric, date, etc., but all expressions must be the same datatype.

Example 1:

The example below shows the usage of COALESCE() function.

COALESCE(NULL, NULL, NULL, 'London', 'Paris')
Result: 'London'

COALESCE('Mumbai', NULL, NULL, 'London', 'Paris')
Result: 'Mumbai'

COALESCE(1, 2, NULL, NULL, 3)
Result: 1

COALESCE(NULL, 10, NULL, 20, 30)
Result: 10

Example 2:

Consider a database table called Product with the following records:

If the OrderQuantity is optional and can contain NULL values. The statement mentioned below will give NULL value.

SELECT Product.*, 
Price * (StockQuantity + OrderQuantity) AS Inventory  
FROM Product;

This will produce the result as shown below:

To avoid such situations, the COALESCE() function can be used to provide an alternative value to the column if it contains NULL value.

SELECT Product.*, 
Price * (StockQuantity + COALESCE(OrderQuantity, 0)) AS Inventory
FROM Product;

This will produce the result as shown below: