All Products
Search
Document Center

COALESCE

Last Updated: Jun 18, 2021

The COALESCE function returns the first non-null expression in a parameter list. You must specify at least two parameters.

Syntax

COALESCE(expr1, expr2[,…, exprn])

Parameters

Parameter

Description

expr1, expr2[,…, exprn]

The non-null expressions. You must specify at least two non-null expressions.

Return type

The first non-null expression in the parameter list is returned. If all the parameters are NULL, NULL is returned.

Examples

Assume that a product_information table is available. In the table, product_id indicates a product ID, list_price indicates the original price of the product, min_price indicates the lowest price of the product, and Sale indicates the actual sale price of the product. Specify the product discount as 10% and calculate the actual sale price of each product. In this case, you can use the COALESCE function. If list_price is empty, perform calculations based on min_price. If min_price is also empty, perform calculations based on 5.

You can execute the following statements to create the product_information data table and insert data into the table:

CREATE TABLE product_information(supplier_id INT, product_id INT,list_price numeric, min_price numeric);
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '1659', '45', NULL);
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '1770', NULL, '70');
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '2370', '305', '247');
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '2380', '750', '731');
INSERT INTO PRODUCT_INFORMATION VALUES ('102050', '3255', NULL, NULL);

Execute the following query statement:

SELECT product_id, list_price,min_price,COALESCE(0.9*list_price, min_price, 5) "Sale" 
FROM product_information WHERE supplier_id = 102050 ORDER BY product_id;

The following query result is returned:

+--------------+--------------+------------+--------+
|  PRODUCT_ID  |  LIST_PRICE  |  MIN_PRICE |  Sale  |
+--------------+--------------+------------+--------+
|        1659  |       45     |            |  40.5  |
+--------------+--------------+------------+--------+
|        1770  |              |     70     |    70  |
+--------------+--------------+------------+--------+
|        2370  |      305     |    247     | 274.5  |
+--------------+--------------+------------+--------+
|        2380  |      750     |    731     |   675  |
+--------------+--------------+------------+--------+
|        3255  |              |            |     5  |
+--------------+--------------+------------+--------+