# 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 optimize 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](https://www.ionos.com/digitalguide/hosting/technical-matters/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](https://www.ionos.com/digitalguide/server/know-how/what-is-sql/) in our tutorial [“Introduction to SQL with examples”](https://www.ionos.com/digitalguide/server/configuration/sql-introduction-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](https://www.ionos.com/digitalguide/hosting/technical-matters/database-management-systems-dbms/). 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**:

```sql
NVL(value1, value2)
```

**ISNULL**:

```sql
ISNULL(value1, value2)
```

**IFNULL**:

```sql
IFNULL(value1, value2)
```

The functions are usually used with [SQL SELECT](https://www.ionos.com/digitalguide/server/configuration/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:

<table>
  <thead>
    <tr>
      <th>SQL function</th>
      <th>Treatment of NULL values</th>
      <th>Compatibility</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>SQL `NVL`</td>
      <td>Replaces NULL values with the specified value</td>
      <td>Oracle, Db2</td>
    </tr>
    <tr>
      <td>SQL `ISNULL`</td>
      <td>Replaces NULL values with the specified value or empty strings</td>
      <td>SQL Server, MySQL, MS ACCESS</td>
    </tr>
    <tr>
      <td>SQL `IFNULL`</td>
      <td>Replaces NULL values with the specified value and does not interpret spaces as NULL values</td>
      <td>MySQL, Google BigQuery</td>
    </tr>
  </tbody>
</table>

## 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](https://www.ionos.com/cloud/sql-server-hosting/ "SQL Server Hosting from IONOS") from IONOS for individualized server and hosting plans.

## Examples of SQL `NVL`

Below we’ll look at some examples that will illustrate how SQL `NVL`, ``ISNULL`and`IFNULL` 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`

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

### Example of SQL `ISNULL`

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

### Example of SQL `IFNULL`

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

## 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](https://www.ionos.com/digitalguide/server/configuration/sql-operators/) and functions is [SQL COALESCE](https://www.ionos.com/digitalguide/server/configuration/sql-coalesce/). `COALESCE` is available in **almost every database** and allows you to replace NULL values.

Its syntax is also very similar to `NVL`:

```sql
COALESCE(Value1, Value2)
```


This is a markdown version of: [https://www.ionos.com/digitalguide/server/configuration/sql-nvl/](https://www.ionos.com/digitalguide/server/configuration/sql-nvl/) for AI/LLM consumption.