Understanding NVL Function in SQL

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.

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

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

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

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

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

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

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

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

• SQL Introduction.
   
• SQL Expression.
   
• NULL Values
   

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.