# How to use the SQL IFNULL() function to output alternative values

The SQL `IFNULL()` function is used to check the value of an expression. If this is NULL, an alternative value is output instead. If it’s not NULL, the system displays its original value.

## What is SQL IFNULL()?

In the [Structured Query Language](https://www.ionos.com/digitalguide/server/configuration/sql-introduction-with-examples/), the [SQL COALESCE()](https://www.ionos.com/digitalguide/server/configuration/sql-coalesce/) function is an important tool. However, to return a specific value when an expression is **empty or NULL**, you can use SQL `IFNULL()` instead. This function checks the expression and performs one of two actions. If the checked value is NULL, it returns an alternative value that you specify in advance. If the value is not NULL, it returns the original expression. While this may seem confusing in theory, a few practical examples will quickly illustrate the function’s usefulness.

## Syntax and function

First, let’s take a look at the basic syntax of SQL `IFNULL()`:

```sql
IFNULL(expression, alternative_expression);
```

The first expression is the one that’s to be checked for its value. The alternative expression is output if the first expression is NULL.

## Examples of using the function

To see how SQL `IFNULL()` works, try these two simple examples. In the first example, the function will recognize that the value of the expression is not NULL. Here is the corresponding code:

```sql
IFNULL('This is the first expression', 'This is the alternative');
```

When you execute the code, the output looks like this:

```sql
This is the first expression
```

The first expression has a value and therefore the function doesn’t have an alternative. The next example is different:

```sql
IFNULL(' ', 'This is the alternative');
```

We get the following result:

```sql
This is the alternative
```

Since the value of the first expression is NULL, the function resorts to the alternative.

Naturally, this also works with numerical values:

```sql
IFNULL(10, 15);
```

We get the following output:

```sql
10
```

Without a stored value, SQL `IFNULL()` is used:

```sql
IFNULL(NULL, 15);
```

The output is:

```sql
15
```

## A practical example

One possible use of SQL `IFNULL()` could be as follows. We have a table called “Deliveries”; this has columns for name, delivery address and billing address:

<table>
  <thead>
    <tr>
      <th>Name</th>
      <th>Delivery Address</th>
      <th>Billing Address</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Smith</td>
      <td>123 Maple Street</td>
      <td>123 Maple Street</td>
    </tr>
    <tr>
      <td>Johnson</td>
      <td>456 Oak Avenue</td>
      <td>789 Pine Boulevard</td>
    </tr>
    <tr>
      <td>Brown</td>
      <td>321 Birch Road</td>
      <td>NULL</td>
    </tr>
    <tr>
      <td>Davis</td>
      <td>987 Cedar Lane</td>
      <td>654 Elm Street</td>
    </tr>
    <tr>
      <td>Wilson</td>
      <td>741 Rosewood Drive</td>
      <td>741 Rosewood Drive</td>
    </tr>
  </tbody>
</table>

One customer has only entered a delivery address and omitted a billing address. To ensure that all entries are complete, use SQL `IFNULL()`. The appropriate code with the [SQL command](https://www.ionos.com/digitalguide/server/configuration/sql-commands/) `SELECT` looks like this:

```sql
SELECT Name, IFNULL(billing address, delivery address) Address 
FROM Deliveries;
```

This gives us a new table in which at least one address is stored for each customer:

<table>
  <thead>
    <tr>
      <th>Name</th>
      <th>Address</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Smith</td>
      <td>123 Maple Street</td>
    </tr>
    <tr>
      <td>Johnson</td>
      <td>456 Oak Avenue</td>
    </tr>
    <tr>
      <td>Brown</td>
      <td>321 Birch Road</td>
    </tr>
    <tr>
      <td>Davis</td>
      <td>987 Cedar Lane</td>
    </tr>
    <tr>
      <td>Wilson</td>
      <td>741 Rosewood Drive</td>
    </tr>
  </tbody>
</table>

## Alternatives to SQL IFNULL()

In addition to the `COALESCE()` function mentioned above, there are some other alternatives to SQL `IFNULL()`. [SQL NVL()](https://www.ionos.com/digitalguide/server/configuration/sql-nvl/) also converts a NULL value to a value of your choice. [SQL ISNULL()](https://www.ionos.com/digitalguide/server/configuration/sql-isnull/) also checks whether a value is NULL or not and then replaces it with a defined value if required.

Tip The perfect database for your needs: With [SQL Server Hosting](https://www.ionos.com/cloud/sql-server-hosting "SQL Server Hosting from IONOS") from IONOS, you can choose between MSSQL, MySQL, or MariaDB and benefit from top performance and strong security features.


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