SQL Tutorial SQL Advanced SQL Database SQL References

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

expression1, expression2, ... expression_n Required. Specify the expressions to test for non-null values. The expressions must all be the same datatype.

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:

ProductNamePriceStockQuantityOrderQuantity
Apple1.0010020
Banana1.2512030
Orange2.15105NULL
Watermelon3.507515

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:

ProductNamePriceStockQuantityOrderQuantityInventory
Apple1.0010020120.0
Banana1.2512030187.5
Orange2.15105NULLNULL
Watermelon3.507515315.0

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:

ProductNamePriceStockQuantityOrderQuantityInventory
Apple1.0010020120.0
Banana1.2512030187.5
Orange2.15105NULL225.75
Watermelon3.507515315.0

❮ Oracle Functions