NVL in SQL

Introduction

SQL, or Structured Query Language, lets you interact with relational databases. Simply put, you can communicate with the database using SQL. You can store, manipulate, and retrieve data through SQL.

Today, we’ll talk about the NVL command in SQL, which replaces the null values with different strings in the query results. However, data is only sometimes perfect.

There have been many examples of missing data leading to null in SQL. How you handle null values becomes essential for data integrity. NVL in SQL can ensure predictable outcomes for data queries. NVL function works like the IFNULL function in MySQL. Let's dive deep and understand what the NVL function is in SQL.

Overview

Ask any new developer about handling null values, and you will receive the same answer. Most developers get frustrated - when it comes to handling null values in building data-based applications. Also, if you don't address null values at the right time, it can lead to errors in the results. You don't want that to happen, right?

That's why it's vital to know the functioning of NVL in SQL. With the use of the NVL function in SQL, the development process will become seamless.

What is NVL in SQL?

NVL in SQL can let you handle null values correctly. Plus, you can replace the null values with specific values. You can find the NVL function in the Oracle database, and it ensures that the operations don't yield unexpected results because of null values.

You can't predefine the NVL function in MySQL, but you can define it in Oracle. If you work in SQL, then IFNULL is defined. The command works similarly in SQL without an added command.

Most importantly, you can replace the Null value with the NVL function. You can expect string values on return. Briefly, the SQL server uses ISNULL. With Oracle - you can use NVL. You can also call NVL as a substitution function that replaces the value with null.

The Syntax for the Function

Let's now see the basic syntax for the NVL function:

SELECT NVL (Value, Substitute) FROM table;

Here NVL function takes two different arguments:

• Value is the first expression, and you can change the value of the given expression.
• Substitute lets you replace the value with the previous value.

However, with the null value, NVL will return as a substitute.

Syntax of NVL in Oracle:

NVL(expression1, replace_with_value)

In expression, the value of the column name might contain a null value.

Replace_ with_ value will return the value if the expression is null.

How can you use NVL in SQL?

Let's learn how you can apply the NVL function in SQL through the help of this example:

employee_name
employee_id
Sara
1011
Abert
NULL
Elliot
1012
NoraI
1010

If you want to replace the null value to employee ID with 1026, you can use the NVL function.

SELECT NVL(employee_id,1026) AS id FROM employee;

The outcome you will see with the NVL function in SQL is:

id
1012
1026
1019

In the above example - you could replace the null value with the integer value. However, if you are working with MySQL, you can use IFNULL.

Practical examples of how NVL works

You can use the NVL function in SQL - as a tool to handle null values. Let's now see some practical examples of how the NVL command works in SQL.

Strings

For example, you can consider that you have a year's table. However, some users do not have an email. In this case, you would want to have a default email placeholder.

SELECT username, NVL(email, 'no-email@codedamn.com') AS user_email
FROM users;

Numbers

Now, imagine you have a table with salaries and different columns for bonuses and names. The column section has all the null values for employees who haven't earned any bonuses.

SELECT name, NVL(bonus, 0) AS effective_bonus
FROM salaries;

The above query ensures that the null values are replaced with zero.

Dates

Now, to the task table. Some tasks may not have an end date. However, with the NVL function, you can set the future date as a placeholder.

SELECT task_name, NVL(end_date, TO_DATE('31-12-9999', 'DD-MM-YYYY')) AS target_end_date
FROM tasks;

Joins

Joins is an advanced example that shows examples of two tables - products and orders. In the example, you don't order all the products but want to list the products with order quantities, setting up the unsold ones with zero.

SELECT p.product_name, NVL(o.quantity, 0) AS ordered_quantity
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id;

Aggregate function

If you want to calculate the average bonus of employees, you can use this function. In some cases - you can set the bonuses to the null value.

SELECT department, NVL(AVG(bonus), 0) AS average_bonus
FROM employee
GROUP BY department;

In the above example, you won't skew results for departments with no bonus.

Types of NVL functions in SQL

Nvl2

Nvl2 in SQL works like the ISNULL function. The only difference is instead of checking one pair, it can check and replace two pairs. Also, you can replace the Oracle nvl2 function with the case statement in the SQL server.

The nvl2 function helps you evaluate the first expression. It will return the second expression if the first expression is not null. Also, if the first expression is null, it will return the third expression.

Nvl2 will return expression two if expression one is not null. Nvl2 returns the expression 2 - if the expression 1 is null. Most importantly, it can include data from the expression one. The syntax you can use is:

SELECT ColumnName1,ColumnName2,…..ColumnNameN,
CASE conditi
WHEN value/expression1 NOT NULL THEN AlternateValue1
WHEN value/expression2 NOT NULL THEN AlternateValue2
ELSE AlternateValue
END
FROM table_name;

Decode

Decode performs the function of case argument, which makes conditional queries easier. The function uses the expression with - if then else logic, which is common in programming languages.

The decode function helps to decode the value after comparing the search value to the expression. Also, you can expect the return result with the same expression. In addition, it acts like a comparison feature that allows you to compare different base values. You can compare base values of up to 255 evaluations in a single call. However, if the base value is not equal to the evaluation - you can expect a default value.

The syntax you can use is:

SELECT ColumnName1,ColumnName2,…..ColumnNameN,
CASE conditi
WHEN value/expression1 NOT NULL THEN AlternateValue1
WHEN value/expression2 NOT NULL THEN AlternateValue2
…..
WHEN value/expressionN NOT NULL THEN AlternateValueN
ELSE AlternateValue
END
FROM table_name;

NULLIF

With the NULLIF command, you can compare two expressions. It is a vital NVL function in SQL. It returns null if they are equal. Also, the function will return the first expression if they are not identical. For the first word, you can use null. The syntax you can use is:

NULLIF (expr_1, expr_2)

Coalesce

You can use the coalesce function in SQL to examine the first expression. If it is not null, it will return. However, it performs Coalesce of the remaining expression.

This function has an advantage over the NVL function as it can take multiple alternative values. Also, it returns the first non-null expression in the sequence. The syntax you can use is:

COALESCE (expr_1, expr_2, ... expr_n)

Nanvl

You can use the Nanvl NVL function in SQL for different floating point numbers. For instance, if the input value n1 is nan, it will instruct the database to return an alternative value. Alternatively, you can convert the nan value to null with the function. The syntax you can use is:

NANVL(n1,n2)

Contrast between NVL and other null functions:

SQL offers many null functions for handling different null values. Let's compare these functions with NVL:

Nullif

Nullif can return null if the two expressions are similar. Otherwise, it sticks to the first expression.

SELECT NULLIF(col1, 'UnwantedValue') AS result
FROM your_table;

Coalesce

Though similar to the NVL in SQL, it can handle multiple arguments. Also, it can retain the first non-null value from the example below:

SELECT COALESCE(col1, col2, col3, 'All are NULL') AS result
FROM your_table;

Nvl2

Nvl2 in SQL is a vital function as it acts like an extension of NVL. Here, if the first expression is not null, it will return the second expression.

SELECT NVL2(col1, 'Has value', 'Is NULL') AS result
FROM your_table;

Performance Considerations of NVL

If you use NVL, it does affect the performance of SQL queries. Though NVL is efficient, it does increase the computation overhead, especially in large datasets. Therefore, it is essential to use NVL properly.

If the column does not contain a null value, there is no point in using the NVL function in SQL. Also, if performance is your main concern, re-consider the query structure.

Common Snags of NVL

There are some snags in NVL you should know about:

When you replace all the null values - it is not the best approach to follow. In addition, too much NVL can lead to convoluted SQL queries, which are hard to debug. As null signifies the absence of value, it can be different from the default value. Mismatches of the datatypes can lead to errors like:

-- This will error if 'salary' is a number
SELECT NVL(salary, 'Not provided') FROM employees;

Conclusion

You can use null as a missing value in SQL. A field with null is similar to - as with no value. However, it is essential to know the difference between zero and null values. Briefly, you can use NVL as a substitution function, which means - with one value.

The NVL function works as an expression depending on use. Hope you are now clear on what NVL in SQL is.

FAQs

1. What is the purpose of NVL?

NVL helps replace the null values with a string of different query results. It acts as a substitution function for the value.

2. How do you use NVL in the sum function?

You can use the NVL() function to handle null values before applying the Sum() function. Here is an example: SELECT department, SUM(NVL(sales, 0)) FROM sales_table GROUP BY department. This query will return the sum of sales in each department while replacing the null values with 0.

3. What is the difference between NVL and Nullif?

NVL checks the first parameter, and NULLIF compares the input parameters.

4. Is NVL faster than coalescing?

Yes, it is faster as you can evaluate input values on Coalesce multiple times.

5. How to use NVL in SQL with an example?

A written example for table- SELECT NVL (Value, Substitute) FROM table;

6. How to convert NULL to 0 in SQL?

UPDATE [table]
SET [column]=0
WHERE [column] IS NULL;

7. How to remove NULL in SQL?

To remove this value - use the IS NOT NULL operator with the WHERE clause, IS NOT NULL operator test for empty values.

8. What is the alternative to the NVL function?

COALESCE function has the same functionality as NVL, and you can use it as an alternative.

9. Is NVL an aggregate function?

You can use NVL as an argument to the aggregate function to substitute the value for a null.

10. How to insert SQL if NULL?

• Using the assignment operator: Use the = operator to insert a null value in any column.
• Using the VALUES clause: Specify the NULL keyword in the VALUES clause to indicate that a column should be assigned a NULL value. For example, INSERT INTO orders (orders_num, order_date, customer_num) VALUES (0, NULL, 123).
• Using the setNull method: Use the setNull method to set a null value into an integer column.