NVL Function in SQL
FREE
Overview
NVL function is defined only in Oracle, not in SQL Server or MySQL. It works like the IFNULL function in MySQL. We can replace the value of NULL or any void present in the existing table with another value of our choice.
What is the NVL Function in SQL?
NVL function is not defined in MySQL or SQL servers but it is defined in Oracle. In SQL servers IFNULL is defined. NVL works the same as the IFNULL function in SQL. To replace the value of NULL, void, empty, or zero we use the NVL function. It returns either numeric or string values.
Syntax
The Syntax of NVL function: -
NVL function takes two arguments.
- Value - The name of the expression whose value is to be changed.
- Substitute - The value you want in place of the previous value. This value will be returned in the result.
If the value is null then NVL will return a substitute else it will return the value itself.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
How to Use the NVL Function in SQL?
Suppose we have the following table named as an employee: -
| employee_name | employee_id |
|---|---|
| Sarah | 1012 |
| Albert | NULL |
| Elliot | 1019 |
| Nora | 1011 |
We want to replace the NULL value with the employee_id to 1025. Now, to do that we will use the NVL function as shown.
This will return the following result.
| id |
|---|
| 1012 |
| 1025 |
| 1019 |
| 1011 |
Wait! What happened just now?
The value of NULL just got replaced with the integer value that we wanted in place of NULL.
If you are working with MySQL workbench or SQL server then there will be an error saying the FUNCTION sys.nvl does not exist. It throws this error because the NVL function is defined only in Oracle. So, if you are working with SQL then use IFNULL.
Various NVL Functions in SQL
There are various functions that work like NVL function. They are: -
- NVL2
- DECODE
- COALESCE
- NULLIF
- NANVL
Let's understand their work and syntax. We won't be looking at these functions in detail here.
1. NVL2
This function first checks if the first expression is null or not. It takes 3 arguments as input. If the first expression is found null then it will return expression2 otherwise it will return expression3. This works for any data type.
Syntax
2. DECODE
It works similarly to IF-THEN-ELSE logic that is used in other programming languages. It will check if the value of an expression is the same as a search. If it is the same then the expression is returned otherwise it will return NULL where the value is not the same as the search value.
Syntax
Turn Learning into Career Growth
3. COALESCE
COALESCE function can take multiple alternate values. It will check if the first expression is null or not. It returns the first non-null value mentioned in the list.
Syntax
4. NULLIF
In the NULLIF function, if the value of both expressions is the same, then it will return null otherwise it will return the value of the first expression.
Syntax
5. NANVL
NANVL is only used for floating numbers that are of the type binary_float or binary_double. If the value of n1 is found as nan then it will return the value of n2. But if the value of n1 is not nan, then it will return the value of n1.
Syntax
Example of NVL Function in SQL
Let's take the data of an employee.
| employee_name | employee_id | address |
|---|---|---|
| Sarah | 1012 | NYC |
| Elliot | 1097 | California |
| Nora | 1024 | NULL |
| Alyesha | 1075 | Canada |
| Nathan | 1011 | NULL |
Now, we have to replace the value of NULL with something valuable like for example "The address does not exist".
So, to do that we will write the following query.
The above query will give the following result.
| employee_name | address |
|---|---|
| Sarah | NYC |
| Elliot | California |
| Nora | The address is not known! |
| Alyesha | Canada |
| Nathan | The address is not known! |
In the above result, the value of NULL got replaced by the string we entered.
Conclusion
- NVL function is used to replace NULL, zero, void, or empty with any value that you want it can be an integer or string.
- NVL function does not exist in MySQL or SQL servers. It is defined only in Oracle.
- Various functions work like the NVL function. For example - NULLIF, NANVL, DECODE, COALESCE, and NVL2.
See Also:
Google
Amazon
Microsoft
Flipkart
Adobe1200+ more