This is the 2nd blog of our Null related functions series.
Today we will talk about COALESCE Null related function.
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.