Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is NVL Function in SQL?
2.1.
SYNTAX
2.2.
Explanation
2.3.
Code Implementation
2.3.1.
Output
2.3.2.
Explanation
2.4.
Code Implementation
2.4.1.
Output
2.4.2.
Explanation
3.
Use Cases of NVL Functions in SQL
3.1.
Used for Simple Case
3.1.1.
Code Implementation
3.1.2.
Output
3.1.3.
Explanation
3.2.
Used for Nested case
3.2.1.
Code Implementation
3.2.2.
Output
3.2.3.
Explanation
4.
Alternative NVL Functions in SQL
4.1.
Code Implementation
4.1.1.
Output
4.2.
NVL2
4.2.1.
Syntax
4.2.2.
Code Implementation
4.2.3.
Output
4.2.4.
Explanation
4.3.
NULLIF
4.3.1.
Syntax
4.3.2.
Code Implementation
4.3.3.
Output
4.3.4.
Explanation
4.4.
NANVL
4.5.
COALESCE
4.5.1.
Syntax
4.5.2.
Code Implementation
4.5.3.
Output
4.5.4.
Explanation
4.6.
DECODE
4.6.1.
Syntax
4.6.2.
Code Implementation
4.6.3.
Output
4.6.4.
Explanation
5.
Difference Between NVL, NVL2, DECODE, COALESCE and NULLIF
6.
Advantages of NVL SQL
7.
Disadvantages of NVL SQL
7.1.
Frequently Asked Questions
7.2.
What is NVL and coalesce in SQL?
7.3.
How do you convert NULL to blank in SQL?
7.4.
How do I convert NULL to blank string?
7.5.
Is a blank string NULL?
7.6.
Can NVL be used to replace values other than NULL?
7.7.
How can NVL be used to improve the readability and performance of SQL queries?
7.8.
Can NVL handle large amounts of databases?
7.9.
Does NVL support all major database management systems?
8.
Conclusion
Last Updated: Mar 27, 2024
Medium

Understanding NVL Function in SQL

Author Vidhi Sareen
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

In SQL, if you want to convert a NULL value to a specific value, then you can use the NVL() method. NULL values represent missing or unknown data. These missing values can cause problems in data analysis. These NULL values cause issues while performing an operation or manipulating data. This is where the NVL method is used. 

NVL in SQL

The NVL method converts a NULL value into a default or a specified value for a presentation or data analysis. To better understand this topic, this article will discuss some important aspects of NVL SQL, like use cases, advantages, disadvantages, or examples.

What is NVL Function in SQL?

The NVL function replaces the NULL value with a specific or default value. It is mainly used by the developer in data analysis and data processing. NVL is not defined in MYSQL but in Oracle. For MYSQL, we can use the IFNULL method. The functionality of both approaches works the same. The syntax for the NVL function depends on the use case you are working on. The basic structure is as follows:

syntax of NVL

SYNTAX

NVL( Expression1, Expression2)

Explanation

In this equation, 'Expression1' will be evaluated for a NULL value, and 'Expression2' is used when 'Expression1' is NULL. NVL SQL can use any data type as an argument, like text, number, or dates.

Let's take a simple example to understand it better. Let's take a table named student with attributes like name, course, and marks.

Code Implementation

Create SQL table

CREATE TABLE SQL_table (
  student VARCHAR(50),
  course VARCHAR(50),
  marks INT
);

 

Insert values into the table

INSERT INTO SQL_table (student, course, marks)
VALUES ('Ninja_1', 'Mathematics', 85);
INSERT INTO SQL_table (student, course, marks)
VALUES ('Ninja_2', 'Science', NULL);
INSERT INTO SQL_table (student, course, marks)
VALUES ('Ninja_3', 'History', 92);
INSERT INTO SQL_table (student, course, marks)
VALUES ('Ninja_4', 'Social Science', NULL);
INSERT INTO SQL_table (student, course, marks)
VALUES ('Ninja_5', 'English', NULL);

 

Print the table

SELECT * FROM SQL_table;

 

Output

output

Explanation

As we can see, some values in the table have been missing. This may create problems while analyzing the performance of students overall. So we need to add some default or specific values instead of NULL values. Let's write a SQL query to solve this issue.

Code Implementation

SELECT DISTINCT student, course, NVL(marks, 0) as marks FROM SQL_table ORDER BY student;

 

Output

output

Explanation

In this case, we have used the NVL function to replace any NULL value with the specific value to 0. The resultant set includes the student, course, and marks columns; The NULL values in the marks column will be replaced with 0. So, this will help in calculating the performance of the student very well.

Also see,  Recursive Relationship in DBMS

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Use Cases of NVL Functions in SQL

There are different ways in which NVL can be used in SQL. Let's take the same table above but with some more missing values for evaluation.

output

Used for Simple Case

They are used in simple cases where NVL can replace a NULL value with a default or a specific value. Taking the above table as an example, we can use the below query to solve a simple problem of NULL values.

Code Implementation

UPDATE SQL_table SET 
  course = NVL(course, 0), 
  marks = NVL(marks, 0)

Output

output

Explanation

We have updated the SQL_table table and set the value of ‘course’ and ‘marks’ to 0 if they are found to be NULL.

Used for Nested case

In this case, we added nested values to the expression. Taking the above table as an example, we can use the below query to solve a nested problem of NULL values.

Code Implementation

SELECT NVL(NVL(course, student), 'Unknown') as course_name
FROM report_card;

Output

output

Explanation

In this statement, we have used a nested NVL statement. The query shows that if the course columns have a NULL value, they will be replaced by the data of the student column. If both columns have NULL values, they will be replaced by 'Unknown.'

Alternative NVL Functions in SQL

As we know that NVL only works with Oracle. So it's important to understand alternative options which work on different servers. Let's take an example and try to implement it using other alternative options of NVL

Code Implementation

Create SQL table

CREATE TABLE report_card (
  student_id INT,
  English INT,
  Hindi INT,
  Maths INT,
  Social_Science INT,
  Science INT
);

 

Insert value in the table

INSERT INTO report_card values (1, '85','75','74','70','77');
INSERT INTO report_card values (2, NULL,'73','55','64','88');
INSERT INTO report_card values (3, NULL,NULL, '89', '52','90');
INSERT INTO report_card values (4, NULL, NULL, '42', '50','76');
INSERT INTO report_card values (5, NULL, NULL, NULL, '84','67');

 

Print

SELECT * FROM report_card

Output

output

Now let's apply different alternative methods to help us remove NULL values in a table.

NVL2

NVL2 is a SQL function which will return a value if the first argument is not NULL else a different value if the first argument is NULL.

  • It is much similar to that of NVL. It allows different expressions to pass through when the first argument is NULL.
     
  • It generally takes three arguments to pass through it.
     
  • The first argument is checked for a NULL value; the second argument is replaced with the first argument if the first argument is not NULL, and the same goes for the third argument.

Syntax

NVL2( exp1, exp2, exp3)

 

Code Implementation

SELECT DISTINCT student_id , NVL2(English, Hindi, Maths) AS MARKS FROM report_card ORDER BY student_id;

 

Output

output

Explanation

We observe that if the first expression is non-NULL, it will be replaced by the second expression value. If the first expression is NULL, then it will be replaced by the third expression value. In this case, 'English' has a value different value:

  • The value of 85 in the English column will be replaced by 75 in the Hindi column.
     
  • There is a NULL value for student_2,3,4,5, so it will be replaced with Maths marks.
     
  • If we have two NULL values for both expressions, it will remain a NULL value.  

NULLIF

NULLIF is a SQL function that is used to compare two expressions and will return NULL if they are found to be equal.

  • It compares two values of the expressions. 
     
  • If the value is the same, it will return a NULL value;
     
  • otherwise, it will take the value of the first argument expression. 

Syntax

NULLIF(exp1, exp2)

 

Code Implementation

SELECT DISTINCT student_id, 
  NULLIF(Hindi, English) AS Fail 
FROM report_card ORDER BY student_id;

 

Output

output

Explanation

In this, we are comparing the marks of 'Hindi' and 'English.' We observe that If Hindi and English have the same value, then it will return NULL. Else it will return the value of Hindi.

NANVL

The Oracle function `NANVL()` is designed to let you specify a default value for parameters that contain the special "NaN" (Not-a-Number) value. It is only used for BINARY_FLOAT and BINARY_DOUBLE data types.

Syntax

NANVL(num1, num2)

COALESCE

COALESCE is a SQL function that returns a non-NULL value of the list of arguments.

  • This function takes multiple alternative values when NULL values.
     
  • It returns a non-NULL value in a list of arguments.
     
  • It will replace the first non-null value. 
     
  • If all the arguments are NULL, then it will return NULL. 

Syntax

COALESCE( exp1, exp2, exp3 ,....expN)

 

Code Implementation

SELECT DISTINCT student_id , COALESCE(English, Hindi, Maths, Social_Science) AS Marks FROM report_card ORDER BY student_id;

 

Output

output

Explanation

We observe that it is returning the first non-NULL in the list.

DECODE

DECODE is a SQL function that returns a value based on a comparison of two expressions.

  • Its working is much similar to that of if-else logic.
     
  • If expression 1 is equal to the expression 2 or both the expression is NULL, then the result expression is returned.
     
  • If there is no match between the expression 1 and expression 2 then the else expression is returned.
     
  • Otherwise a null value is returned.
     

Syntax

DECODE( exp, searched value1, result1 [,searched value2, result2] ….[,default])

 

Code Implementation

SELECT DISTINCT student_id , DECODE(English, NULL, 0, English) AS Marks FROM report_card ORDER BY student_id;

 

Output

output

Explanation

We observe that it is assigning all the NULL values to 0 for the ‘English’ subject.

Difference Between NVL, NVL2, DECODE, COALESCE and NULLIF

FUNCTION PURPOSE SYNTAX
NVL Converts all NULL values with a specific value or a default value NVL(exp, value)
NVL2 Uses three arguments in this method. NVL2( exp1, exp2, exp3)
DECODE It uses If-Else logic to replace the NULL value. DECODE( exp, searched value1, result1 [,searched value2, result2] ….[,default])
COALESCE Returns the first non-NULL value in the list of arguments COALESCE( exp1, exp2, exp3 ,....expN)
NULLIF Returns NULL if the two expressions are equal; otherwise, 0 NULLIF(exp1, exp2

Advantages of NVL SQL

There are some advantages of NVL SQL, which are as follows: 

  • Very easy to handle NULL values while using this function.
     
  • It can be used to substitute any expression or a NULL value.
     
  • It can be used in a UPDATE as well as a SELECT query.
     
  • To tackle complex situations, it can be utilised in layering functions.
     
  • It's used to set default values for a column. 

Disadvantages of NVL SQL

There are some disadvantages of NVL SQL, which are as follows: 

  • The use of the NVL function in a code can cause issues with reading and altering.
     
  • This can cause issues when dealing with large amounts of data.
     
  • It should be used with caution so that it does not cause problems for non-NULL values.
     
  • It may also lead to data loss.
     
  • It can also conceal underlying issues.

    Also see,  Checkpoint in DBMS

Frequently Asked Questions

What is NVL and coalesce in SQL?

In SQL, NVL and COALESCE are used to handle NULL values in queries. NVL replaces a NULL value with a specified default, while COALESCE returns the first non-NULL value from a list of expressions.

How do you convert NULL to blank in SQL?

To convert NULL values to blanks (empty strings) in SQL, you can use the COALESCE function with an empty string as the replacement value. Example: COALESCE(column_name, '').

How do I convert NULL to blank string?

To convert NULL values to a blank string in SQL, you can use the ISNULL or COALESCE function with an empty string as the replacement value, like this: ISNULL(column_name, '') or COALESCE(column_name, '').

Is a blank string NULL?

No, a blank string (an empty string "") is not the same as NULL. NULL represents the absence of a value, while a blank string is a valid string with no characters.

Can NVL be used to replace values other than NULL?

NVL can replace values like empty strings or zeroes other than NULL. You can replace empty strings with the "N/A" value, and zeroes can be converted to 1.

How can NVL be used to improve the readability and performance of SQL queries?

NVL can be used to convert lengthy or repetitive statements into one query. This will make it more concise and more readable to the user. 

Can NVL handle large amounts of databases?

NVL can handle large datasets, but the performance could be better. It is always recommended not to use the NVL function for large datasets to get optimized performance.

Does NVL support all major database management systems?

NVL function does not support all database management systems. It works explicitly on Oracle databases. We can use similar functions like ISNULL, COALESCE, and many more, which work similarly to NVL.

Conclusion

NVL function is a powerful and essential tool for handling database NULL values. It is very effective to use the NVL function if you want to replete the same statement repeatedly. NVL is supported in the Oracle database management system. Other functions show the same functionality as the NVL function. They are NVL2, DECODE, NULLIF, and NANVL. All these functions have their unique advantages and use cases. The developer can choose the more optimized function according to his use case.

To learn more about this topic, check out the link below

You can find more informative articles or blogs on our platform. You can also practice more coding problems and prepare for interview questions from well-known companies on your platform, Coding Ninjas Studio.

Previous article
Data Independence in DBMS
Next article
SQL vs MYSQL
Live masterclass