How to use SQL NVL

SQL’s NVL function replaces null values in datasets and databases with mean­ing­ful values. That helps to stream­line databases, improve the read­ab­il­ity of analyses and reports and optimise per­form­ance for search queries. NVL stands for ‘null value’.

What is SQL NVL?

Ad­min­is­trat­ors 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 in­ac­curacies. That’s where SQL’s NVL comes in. The ‘null-value logic’ function helps you to replace empty columns and fields with mean­ing­ful, user-defined values or strings. It improves the quality and usability of datasets and thus also the per­form­ance 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 auto­mat­ic­ally replaces NULL values with the in­form­a­tion 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.

What’s the dif­fer­ence 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 man­age­ment system. Below we’ll give an overview that should help you choose the right function for replacing NULL values.

Syntax and para­met­ers

The SQL functions NVL, ISNULL und IFNULL all have two para­met­ers, 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)

ISNULL:

ISNULL(value1, value2)

IFNULL:

IFNULL(value1, value2)

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

Dif­fer­ences between SQL NVL, ISNULL and IFNULL

While these three functions are very similar, they do have some dif­fer­ences:

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 in­form­a­tion or addresses with readable values like ‘Unknown’
• Replacing unknown account balances or trans­ac­tions with the numerical value ‘0’
• Replacing empty fields in patient data, financial data or pro­duc­tion data with standard values or average values
• Replacing missing ratings or feedback with ‘N/A’
• Replacing missing images with ‘Image not available’

Examples of SQL NVL

Below we’ll look at some examples that will il­lus­trate 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

Example of SQL ISNULL

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

Example of SQL IFNULL

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

What are SQL NVL al­tern­at­ives?

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 al­tern­at­ive 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)