How to use SQL NVL

SQL’s NVL function replaces null values in datasets and databases with meaningful values. That helps to streamline databases, improve the readability of analyses and reports and optimise performance for search queries. NVL stands for ‘null value’.

What is SQL NVL?

Administrators that regularly work with databases are familiar with the problem with NULL values and the need for complete datasets. Gaps and missing entries can lead to distorted results, unusable reports and other inaccuracies. That’s where SQL’s NVL comes in. The ‘null-value logic’ function helps you to replace empty columns and fields with meaningful, user-defined values or strings. It improves the quality and usability of datasets and thus also the performance of your databases.

How does SQL NVL work?

SQL NVL is simple: You define which value you want to replace NULL values with in your target results. The function checks the records in question for true or false values and automatically replaces NULL values with the information you’ve indicated. NULL values usually represent a lack of data in a row or column. They are not the same as the numerical value ‘0’ or spaces.

Note: SQL NVL is mostly used in ORACLE databases. In MySQL or in SQL Server, ISNULL takes the place of the NVL function. If you want to replace NULL values in databases, use ISNULL in SQL Server and MS ACCESS. In MySQL use the function IFNULL or COALESCE. The functions are all used the same way.

Tip

Learn the basics of SQL in our tutorial ‘Introduction to SQL with examples’.

What’s the difference between NVL, ISNULL and IFNULL?

It’s easy to get confused between NVL, ISNULL and IFNULL when working with different kinds of databases. The three functions are very similar but **not all are available in every database and database management system. Below we’ll give an overview that should help you choose the right function for replacing NULL values.

Syntax and parameters

The SQL functions NVL, ISNULL und IFNULL all have two parameters, which we’ll display here as ‘value1’ and ‘value2’:

  • value1: Specifies the record or column where you want to find and replace null values.
  • value2: Stands for the value that you want to replace NULL values with. Some common choices for filling gaps are ‘N/A’, ‘0’ and ‘Unknown’.

Here is the syntax for the three functions:

NVL:

NVL(value1, value2)
sql

ISNULL:

ISNULL(value1, value2)
sql

IFNULL:

IFNULL(value1, value2)
sql

The functions are usually used with SQL SELECT and FROM, which specify which records should be checked.

Differences between SQL NVL, ISNULL and IFNULL

While these three functions are very similar, they do have some differences:

SQL function Treatment of NULL values Compatibility
SQL NVL Replaces NULL values with the specified value Oracle, Db2
SQL ISNULL Replaces NULL values with the specified value or empty strings SQL Server, MySQL, MS ACCESS
SQL IFNULL Replaces NULL values with the specified value and does not interpret spaces as NULL values MySQL, Google BigQuery

What is SQL NVL used for?

There are many scenarios where you might need to replace NULL values. Here are just a few:

  • Replacing missing customer information or addresses with readable values like ‘Unknown’
  • Replacing unknown account balances or transactions with the numerical value ‘0’
  • Replacing empty fields in patient data, financial data or production data with standard values or average values
  • Replacing missing ratings or feedback with ‘N/A’
  • Replacing missing images with ‘Image not available’
Tip

You’re looking for efficient data management with reliable performance, scalability and fail-safes? Check out SQL Server Hosting from IONOS for individualised server and hosting packages.

VPS Hosting
Fully virtualised servers with root access
  • Unlimited traffic and up to 1 Gbit/s bandwidth
  • Fast SSD NVMe storage
  • Free Plesk Web Host Edition

Examples of SQL NVL

Below we’ll look at some examples that will illustrate how SQL NVL, ``ISNULLandIFNULL` are used. The examples will use a customer table with columns for address, age and customer ID. We’ll replace any potential NULL values under ‘Address’ with ‘N/A’.

Example of SQL NVL

SELECT  Address, Age, CustomerID, NVL(Address,  'N/A')
FROM  customers
sql

Example of SQL ISNULL

SELECT  Address, Age, CustomerID, ISNULL(Address,  'N/A')
FROM  customers
sql

Example of SQL IFNULL

SELECT  Address, Age, CustomerID, IFNULL(Address,  'N/A')
FROM  customers
sql

What are SQL NVL alternatives?

The functions NVL, ISNULL and IFNULL are almost identical. Be sure that the function you want to use is available in your database. Another very similar alternative among the SQL commands, SQL operators and functions is SQL COALESCE. COALESCE is available in almost every database and allows you to replace NULL values.

Its syntax is also very similar to NVL:

COALESCE(Value1, Value2)
sql
Was this article helpful?