Oracle SQL_Null related functions-2_COALESCE

admin Oracle

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.

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

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”.

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!

You May Also Like..

Oracle-Datafile

Hello guys, Hope you are doing fantastic being an Oracle professional. I wrote a blog on Oracle TABLESPACE. If you […]

Oracle Virtual Column

Hello guys, Hope you are doing well. Today, we will talk about the Oracle Virtual Column. I will try to […]

Oracle-Tablespace

Hello guys, Hope you are doing well. Today, we will talk about Oracle Tablespace and I will try to cover […]

5 Comments

  1. I wanted to put you the little bit of note in order to give many thanks once again for those fantastic suggestions you have discussed above. This has been quite seriously open-handed with you to present without restraint precisely what numerous people would have advertised as an ebook to earn some cash on their own, most importantly given that you could possibly have done it if you wanted. Those suggestions in addition worked like the easy way to be certain that other individuals have a similar desire the same as my very own to know the truth very much more when it comes to this issue. I believe there are some more fun situations up front for individuals who view your blog.

  2. I simply wanted to thank you very much yet again. I’m not certain what I would’ve followed in the absence of these opinions contributed by you over my situation. It truly was a horrifying crisis in my circumstances, nevertheless noticing your expert strategy you handled it forced me to cry over fulfillment. I am just happy for your assistance and then hope you recognize what a powerful job you’re undertaking instructing the mediocre ones all through your web page. I’m certain you’ve never come across all of us.

  3. I not to mention my friends were found to be reading through the excellent tactics on the blog then instantly came up with a horrible feeling I never expressed respect to the web blog owner for them. These men became so glad to see all of them and have undoubtedly been loving those things. Many thanks for genuinely very thoughtful as well as for using such smart subjects most people are really eager to discover. Our honest regret for not saying thanks to earlier.

  4. I simply wanted to thank you so much all over again. I am not sure the things that I would’ve worked on in the absence of the entire techniques contributed by you on this question. It actually was an absolute fearsome concern for me, however , coming across the professional manner you processed the issue took me to cry with happiness. I’m thankful for your guidance and in addition pray you recognize what a powerful job you are always carrying out educating many people through your webpage. I am sure you have never met any of us.

Leave a Reply

Your email address will not be published. Required fields are marked *