As we all know that unlike other programming languages NULL in oracle database does not signify zero or a blank space rather it is a value that is unavailable, unassigned, unknown, or inapplicable.
Thus NULL value can a problem and requires intensive handling for driving an accurate result.
Problems!!! What kind of problems?
Glad you asked.
- Result is always NULL.
If you are performing any arithmetic expression containing Null as an operand then oracle will evaluate that expression as null. As you can see here.
- Hard to distinguish.
Though some graphic user tools such as SQL Developer will show you NULL value stored in a column explicitly but in case you are working on your database using command prompt, Linux terminal or SQL*Plus interface, then in most of the cases these tools show nothing in place of null.
Now the question arises here is how to overcome these problems?
To handle NULL in oracle database we have a few pre-defined functions which come as freebies in the package. These functions are NVL, NVL2, NULLIF, COALESCE and many more.
So let’s dig in and see what are these functions for and what are they capable of doing. We will start with the first function NVL.
NVL NULL Function
Using NVL function you can substitute a value in the place of NULL values. The substituted value then temporarily replaces the NULL values in your calculations or expression. Remember that the substituted value only replaces the NULL value temporarily for the session and does not affect the value stored in the table.
Here is the syntax of NVL function.
NVL (exp, replacement-exp)
As you can see NVL function takes two parameters exp and replacement exp. First parameter exp can be a column name of a table or an arithmetic expression and the second parameter replacement expression will be the value which you want to substitute when a NULL value is encountered.
Always remember the data type of both the parameters must match otherwise the compiler will raise an error.
SELECT NVL (commission_pct, 0) FROM employees
On execution all the null values in the result set will get replaced by 0. Similarly we can use NVL null function while performing arithmetic expression. Again let’s take the same arithmetic expression which we used in the previous query where we added 100 to the values of commission pct column.
SELECT NVL(commission_pct,0), NVL(commission_pct,0)+100 FROM employees WHERE salary>13000;
That’s all about NVL null function. Hope you liked the article. I will share an in-depth explanation about other null functions in subsequent blog articles so stay tuned. Kindly share this on your social network and help me reach out to more people. Till then take care & have a great day!