Similar to NVL ( ) and NVL2 ( ) functions which we have seen in previous tutorial, COALESCE ( ) is also a function which will help you in handling null values stored. However unlike NVL ( ) and NVL2 ( ) functions, COALESCE ( ) function does not restrict you with the number of parameters you pass to the function.
Coalesce ( ) function can take N number of arguments and as a result it returns the first not null parameter from the argument list. If in case all the arguments are evaluated as null then this function returns NULL as a result.
Syntax
COALESCE (expr1, expr2… exprn);
Examples of COALESCE ( ) Function In Oracle Database
In order to better explain you the working of COALESCE ( ) function let’s start with some simple examples.
Example 1:
SELECT COALESCE (null, 1, 2, 3) FROM dual;
As you can see in the above example we have passed 4 parameters for the COALESCE ( ) function from which the first parameter is NULL and as mentioned before, a COALESCE ( ) function returns the first ‘Not Null’ value from the parameter list. Thus on execution the result will be 1 which is the first ‘Not Null’ value in the argument list.
Example 2:
SELECT COALESCE (‘Rebellion’, ‘RIDER’, null) FROM dual;
In this example I have passed three parameters to the COALESCE ( ) function in which the first two parameters are character string and the third parameter is a NULL. On successful execution this query will return the first ‘Not Null’ value from the argument list, which is the string ‘Rebellion’ in our case.
The core purpose of above two examples is to make you understand How COALESCE ( ) function works. Now let’s take a step ahead and see a slightly more complex yet easy to understand example.
Example 3:
For the demonstration of this example I have created a table with the name AVENGERS which has four columns:
- First Name
- Last name
- Mobile number
- Landline
For better understanding of the concepts I have also inserted some rows into this table.
Now suppose you have to write a query or say make a report to list the name of all the avengers along with their contact numbers. However what if I say that some of the avengers only have mobile numbers or some only have landline numbers? In this case the possible queries that may strike your mind would be:
SELECT first_name, last_name, mobile FROM avengers;
Or
SELECT first_name, last_name, landline FROM avengers;
Both the queries are correct but the only problem with them is that you will not be able to get the contact of all those avengers who don’t have either mobile number query1 or landline number query 2.
There are two solutions to this problem. First is to write a query which will return the data from both landline and mobile columns.
SELECT first_name, last_name, mobile, landline FROM avengers;
The second and more efficient solution of this problem is that you can use COALESCE function on the columns landline and mobile. That will make your report clearer.
SELECT first_name, last_name, COALESCE (mobile, landline) AS contact FROM avengers;
That’s all about Coalesce Null Function In Oracle Database. You can watch the tutorial too. Hope it helped you in understanding this concept better. Please share it with your friends on social network and help me reach out to more people. Thanks and have a great day!