DB GEEK

Hello guys,
This is the 2nd blog of our Null related functions series.
Today we will talk about COALESCE Null related function.

COALESCE Function

Syntax:
COALESCE(expr1, expr2,exprN)

In the above syntax, expr1 and expr2 can be columns from the table as well as any value of any data type.
You must specify at least two expressions to the function and at least one expression must be a column name if you are query to a database table.

COALESCE function returns the first not-null value out of string of expressions. If it finds a not-null expr, it will ignore the rest expressions. If all the expressions evaluate to null, then the COALESCE function will return null.

This function is little bit similar as NVL and Case functions. I have already written a blog on NVL function. If you haven’t read it then click here and read that blog.
A detailed blog on Case function is on the way.

Let’s create a fresh table with some data in it and then see how COALESCE function works.

CREATE TABLE product_information (
    product_id   NUMBER(10),
    list_price   NUMBER(5),
    min_price    NUMBER(5)
);

Output…
Table product_information created.

Now, let’s insert some rows in table product_information.

INSERT INTO PRODUCT_INFORMATION (PRODUCT_ID, LIST_PRICE, MIN_PRICE) 
VALUES (1769.0, 48.0, NULL);

INSERT INTO PRODUCT_INFORMATION (PRODUCT_ID, LIST_PRICE, MIN_PRICE) 
VALUES (1770.0, NULL, 73.0);

INSERT INTO PRODUCT_INFORMATION (PRODUCT_ID, LIST_PRICE, MIN_PRICE) 
VALUES (2378.0, 305.0, 247.0);

INSERT INTO PRODUCT_INFORMATION (PRODUCT_ID, LIST_PRICE, MIN_PRICE) 
VALUES (2382.0, 850.0, 731.0);

INSERT INTO PRODUCT_INFORMATION (PRODUCT_ID, LIST_PRICE, MIN_PRICE) 
VALUES (3355.0, NULL, NULL);
COMMIT;


Output…

1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
Commit complete.

Tip: you can write all the insert statements, select all statements and execute them using F5 function key. SQL Developer will manage to run these SQL statements one by one.

So we have created a table and inserted 5 row into the table. Also, we inserted some null values into the table. Let’s assume a scenario.
Shopkeeper gives a 10% discount to all products with a list price. If there is no list price, then the sale price is the minimum price. If there is no minimum price, then the sale price is “5”.

SELECT
    product_id,
    list_price,
    min_price,
    coalesce(0.9 * list_price,min_price,5) "Sale"
FROM
    product_information;

Output…

PRODUCT_ID LIST_PRICE  MIN_PRICE       Sale
---------- ---------- ---------- ----------
      1769         48                  43.2
      1770                    73         73
      2378        305        247      274.5
      2382        850        731        765
      3355                                5

For the product_id – 1769, it calculates discount of 10% on List_Price and finds the first not-null value and passes it to “Sale” column. For the product_id – 1770, we do not have List_price so it finds the first not-null value in the min_price column and passes to “Sale” column.
Same for the product_id – 3355, it does not find not-null values in list_price and min_price columns so it passes 5 (which is 3rd expression in the COALESCE function) to “Sale” column.
That’s it for COALESCE function.

If you have any questions please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil

Sharing is caring!