MySQL IFNULL Function Guide: Replace NULL Values (Oracle NVL Alternative)

1. Introduction

When working with databases, handling NULL values is a critical point.
In particular, it’s not uncommon for people who are used to Oracle’s NVL function to realize that NVL is not available when migrating to MySQL.

In MySQL, you can properly handle NULL values by using the IFNULL function instead of NVL.
This article explains how to work with NULL values in MySQL in detail, covering how to use IFNULL and how it differs from other NULL-handling functions.

1.1 What is a NULL value?

In databases, NULL means “no value has been set.”
Because this is different from “0” or an “empty string”, failing to handle it properly can lead to unexpected errors or incorrect query results.

For example, assume you have data like the following.

In the data above, the age for ID “2” (Sato) is NULL.
If you run calculations as-is, you may get errors or unintended results.

1.2 How MySQL handles NULL values

2. What is the IFNULL function?

MySQL provides the IFNULL function as a function to replace NULL values with another value.
It plays a role similar to Oracle’s NVL function.

By handling NULL values appropriately, you can prevent calculation issues caused by missing data and build more stable SQL queries.
Let’s take a closer look at the basic usage of IFNULL.

2.1 Basic syntax of IFNULL

The basic syntax of the IFNULL function is as follows.

IFNULL(expression, fallback_value)

• expression: The column or value to check for NULL
• fallback_value: The value to return when the expression is NULL (if not NULL, the expression is returned as-is)

For example, consider the following SQL statement.

SELECT IFNULL(NULL, 'Fallback value');

In this case, because NULL is specified, the result becomes 'Fallback value'.

On the other hand, if you specify a non-NULL value, that value is returned as-is.

SELECT IFNULL('Hello', 'Fallback value');

2.2 Key characteristics of IFNULL

The IFNULL function has the following characteristics.

1. It can convert NULL values into a specific value

• You can set an alternative default value for columns that may contain NULL.

1. It uses a simple syntax

• It’s simpler to write than using a CASE expression.

1. You should consider data types

• It’s recommended that the arguments to IFNULL are the same data type.

For example, the following SQL can cause an error.

SELECT IFNULL(100, 'Error');

Reason: A numeric type (100) and a string type (‘Error’) are mixed.
In this case, the fallback value should also be numeric.

SELECT IFNULL(100, 0);

2.3 When you should use IFNULL

Here are some practical situations where IFNULL is useful.

1. Set a default value for NULL

• For example, if an employee’s bonus is NULL, set it to 0.

   SELECT name, IFNULL(bonus, 0) AS bonus
   FROM employees;

2. Avoid calculations involving NULL

• If you calculate with NULL as-is, the result also becomes NULL.
• Using IFNULL to avoid NULL enables the intended calculation.

   SELECT name, salary, IFNULL(bonus, 0) AS bonus, salary + IFNULL(bonus, 0) AS total_income
   FROM employees;

3. Handle NULL properly in reports and aggregations

• If NULL is present during analysis, incorrect reports may be produced.
• By replacing NULL with a specific value using IFNULL, you can process data consistently.

3. Practical examples of the IFNULL function

In the previous section, we explained the basics of the IFNULL function.
In this section, we’ll introduce concrete examples of using it with real data.

3.1 Replace NULL values with a default value

If a table contains NULL values, it may cause unintended behavior.
To solve this, you can use IFNULL to replace NULL with a default value.

Example: If an employee’s bonus is NULL, set the default value to 0

SELECT name, IFNULL(bonus, 0) AS bonus
FROM employees;

Data before execution

After applying IFNULL

Replacing NULL with 0 makes aggregation and related processing smoother.

3.2 Avoid calculations that include NULL

In MySQL, the result of a calculation that includes NULL becomes NULL.
Therefore, you need to use IFNULL to avoid NULL.

Example: Calculate the total of salary and bonus

SELECT name, salary, IFNULL(bonus, 0) AS bonus, salary + IFNULL(bonus, 0) AS total_income
FROM employees;

Data before execution

After applying IFNULL

If the bonus is NULL, the total (salary + bonus) also becomes NULL,
but by applying IFNULL, MySQL treats NULL as 0 and calculates correctly.

3.3 Replace NULL with another string

You can set a default string not only for numbers, but also when the value is NULL.

Example: Show “Not registered” for users without an email address

SELECT id, name, IFNULL(email, 'Not registered') AS email
FROM users;

Data before execution

After applying IFNULL

If left as NULL, the field appears blank, but IFNULL makes it explicit with “Not registered.”

3.4 Use IFNULL in a WHERE clause

You can use IFNULL in a WHERE clause to filter based on conditions that include NULL values.

Example: Retrieve only users with NULL values

SELECT *
FROM users
WHERE IFNULL(email, '') = '';

This SQL treats email as '' (an empty string) when it is NULL, and retrieves only users whose email is NULL.

3.5 Put NULL values last in ORDER BY

Normally, when you use ORDER BY, NULL values may appear first, but you can use IFNULL to move them to the end.

Example: Place rows with NULL values last

SELECT name, salary
FROM employees
ORDER BY IFNULL(salary, 0) ASC;

4. Differences between IFNULL and COALESCE

MySQL provides multiple functions for handling NULL values, and IFNULL and COALESCE are often compared.
Both replace NULL values with alternatives, but their usage and behavior differ.

In this section, we explain the differences between IFNULL and COALESCE and how to choose the right one.

4.1 What is the COALESCE function?

The COALESCE function returns the first non-NULL value among multiple arguments.
In other words, while IFNULL selects the non-NULL value between two values,
COALESCE differs by selecting the first non-NULL value from multiple candidates.

Syntax

COALESCE(expr1, expr2, ... , exprN)

• Evaluates from left to right and returns the first non-NULL value
• Returns NULL if all arguments are NULL

Example: Replacing NULL using COALESCE

SELECT name, COALESCE(phone, email, 'Not registered') AS contact_info
FROM customers;

In this case, values are determined as follows.

1. If phone is not NULL, return phone.
2. If phone is NULL and email is not NULL, return email.
3. If both phone and email are NULL, return 'Not registered'.

4.2 Differences between IFNULL and COALESCE

4.3 Practical examples of IFNULL and COALESCE

Example 1: Simple NULL replacement

With IFNULL, you can select the non-NULL value between two values.

SELECT name, IFNULL(phone, 'Not registered') AS contact_info
FROM customers;

Result

Example 2: Prefer the first available non-NULL value

With COALESCE, you can retrieve the first non-NULL value.

SELECT name, COALESCE(phone, email, 'Not registered') AS contact_info
FROM customers;

Result

• If phone is not NULL, return phone
• If phone is NULL and email is not NULL, return email
• If both phone and email are NULL, return 'Not registered'

4.4 How to choose between IFNULL and COALESCE

✔ When you should use IFNULL

✅ When you want a simple replacement of NULL with a default value
✅ When two arguments are enough (e.g., convert NULL to 0)

✔ When you should use COALESCE

✅ When you want to find the first non-NULL value (e.g., phone → email → “Not registered”)
✅ When you need to evaluate three or more values

4.5 Performance comparison between IFNULL and COALESCE

In general, IFNULL is faster than COALESCE.
This is because IFNULL evaluates only two values, while COALESCE evaluates multiple values in order.

Performance test

When applying IFNULL and COALESCE to 1 million rows, you may see results like the following.

➡ With large data volumes, IFNULL can be slightly faster.
➡ However, if you only need one fallback, use IFNULL; if you want multiple candidates, use COALESCE.

5. NULL-handling functions in databases other than MySQL

MySQL provides the IFNULL function to handle NULL values, but other database management systems (DBMS) use different functions.
In major databases such as Oracle, PostgreSQL, and SQL Server, it’s common to use functions that differ from MySQL for NULL handling.

This section explains how NULL values are handled in databases other than MySQL.

5.1 NULL handling in Oracle: the NVL function

In Oracle, the NVL function is provided as the equivalent of MySQL’s IFNULL.
The NVL function returns another value when the specified value is NULL.

Syntax

NVL(expression, fallback_value)

• expression: The column or value to check for NULL
• fallback_value: The value to return when NULL (if not NULL, the expression is returned as-is)

Example

SELECT name, NVL(salary, 0) AS salary
FROM employees;

Result

The behavior of NVL is almost the same as MySQL’s IFNULL, so in Oracle you can simply use NVL.

5.2 NULL handling in PostgreSQL and SQL Server: the COALESCE function

In PostgreSQL and SQL Server, the COALESCE function is used to replace NULL values with alternatives.
This function can return the first non-NULL value among multiple candidates.

Syntax

COALESCE(expr1, expr2, ..., exprN)

• Evaluates from left to right and returns the first non-NULL value
• Returns NULL if all arguments are NULL

Example

SELECT name, COALESCE(phone, email, 'Not registered') AS contact_info
FROM customers;

Result

As shown above, in PostgreSQL and SQL Server, using COALESCE allows more flexible NULL handling than MySQL’s IFNULL.

5.3 Comparison of NULL-handling functions across databases

• Simple NULL handling → IFNULL (MySQL) or NVL (Oracle)
• Select the best value from multiple candidates → COALESCE (PostgreSQL, SQL Server)

5.4 Notes when migrating between different DBMS

When migrating systems between different databases, you need to pay attention to differences in NULL-handling functions.
In particular, when migrating from Oracle to MySQL, you must rewrite NVL to IFNULL.

Rewrite examples during migration

• Oracle (NVL)

  SELECT NVL(salary, 0) AS salary FROM employees;

• MySQL (IFNULL)

  SELECT IFNULL(salary, 0) AS salary FROM employees;

• PostgreSQL / SQL Server (COALESCE)

  SELECT COALESCE(salary, 0) AS salary FROM employees;

Also, because COALESCE can accept multiple arguments, it is more flexible than Oracle’s NVL or MySQL’s IFNULL.
During migration, it’s important to choose the correct function for the target database.

6. Frequently Asked Questions (FAQ)

Questions about MySQL’s IFNULL function and NULL handling are important for developers and database administrators.
This section summarizes common questions about IFNULL.

Q1. Are the IFNULL function and the NVL function the same?

➡ They provide almost the same functionality, but the function name differs depending on the database.

In MySQL, use IFNULL; in Oracle, use NVL to convert NULL values to a fallback value.

Q2. What is the difference between IFNULL and COALESCE?

➡ IFNULL returns the non-NULL value between two arguments, while
COALESCE returns the first non-NULL value among multiple arguments.

Examples

SELECT IFNULL(NULL, 'Fallback value'); -- Result: 'Fallback value'
SELECT COALESCE(NULL, NULL, 'First non-NULL value'); -- Result: 'First non-NULL value'

✔ When you should use IFNULL
✅ Return a specific default value when NULL (e.g., if NULL then 0)
✅ When you only compare two values

✔ When you should use COALESCE
✅ Retrieve the first non-NULL value (e.g., phone → email → default value)
✅ When you need to evaluate three or more values

Q3. Can IFNULL be used with data types other than numbers?

➡ Yes. IFNULL can be used with strings, dates, numbers, and more.

Example 1: Using strings

SELECT name, IFNULL(email, 'Not registered') AS email
FROM users;

Example 2: Using dates

SELECT name, IFNULL(last_login, '2000-01-01') AS last_login
FROM users;

However, mixing different data types (such as numbers and strings) can cause errors, so be careful.

SELECT IFNULL(100, 'Error'); -- May cause an error due to different data types

Q4. Does using IFNULL reduce performance?

➡ In general, the performance impact is minimal, but it can matter when processing large volumes of data.

• IFNULL is usually fast because it only checks two values
• However, heavy use of IFNULL on very large datasets (millions of rows) can affect index optimization in some cases

🔹 Performance optimization tips

1. Set indexes appropriately

• Queries like IFNULL(column, 0) = 100 may prevent indexes from being used in some cases
• One approach is to store values with an appropriate default instead of NULL from the beginning

1. IFNULL is lighter than COALESCE

• Because COALESCE evaluates multiple values in order, IFNULL can be faster in many cases

Q5. Can you use CASE instead of IFNULL?

➡ Yes. You can achieve similar behavior with CASE, but it becomes more verbose.

With IFNULL

SELECT name, IFNULL(salary, 0) AS salary
FROM employees;

With CASE

SELECT name,
  CASE WHEN salary IS NULL THEN 0 ELSE salary END AS salary
FROM employees;

✔ IFNULL is concise and easy to use
✔ CASE supports more flexible conditions (e.g., you can include non-NULL conditions as well)

Q6. Can IFNULL be used in a WHERE clause?

➡ Yes. IFNULL can be used inside a WHERE clause.

Example: Replace NULL with a specific value when searching

SELECT * FROM users WHERE IFNULL(status, 'Not set') = 'Not set';

This retrieves records where status is NULL.

Summary

• IFNULL and NVL have almost the same functionality, but differ by DBMS
• IFNULL evaluates 2 values; COALESCE evaluates multiple values
• Works with strings, dates, numbers, and more
• For large datasets, consider index optimization
• You can use CASE instead of IFNULL
• IFNULL can also be used in WHERE clauses

7. Conclusion

In this article, we covered MySQL’s IFNULL function in detail, including how to handle NULL values, how it differs from other functions, and practical examples.
Finally, let’s briefly review what we covered.

7.1 What is the IFNULL function?

• IFNULL returns a fallback value when the specified value is NULL
• Syntax:

  IFNULL(expression, fallback_value)

• By avoiding NULL, you can prevent calculation errors and missing-data issues

7.2 Practical examples of IFNULL

• Replace NULL with a default value (0 or a specific string)

  SELECT name, IFNULL(bonus, 0) AS bonus FROM employees;

• Correctly handle calculations that include NULL

  SELECT name, salary + IFNULL(bonus, 0) AS total_income FROM employees;

• Convert NULL to an appropriate string such as “Not registered”

  SELECT id, IFNULL(email, 'Not registered') AS email FROM users;

• Use in WHERE clauses to filter NULL values

  SELECT * FROM users WHERE IFNULL(status, 'Not set') = 'Not set';

7.3 Differences between IFNULL and COALESCE

• IFNULL returns the non-NULL value between two arguments
• COALESCE returns the first non-NULL value among multiple arguments
• How to choose
• Simple NULL handling → IFNULL
• Select the first non-NULL value → COALESCE

7.4 NULL handling in other DBMS

• Oracle’s NVL is almost the same as MySQL’s IFNULL
• PostgreSQL and SQL Server commonly use COALESCE
• During migration, you must replace functions appropriately

7.5 Performance and cautions for IFNULL

• IFNULL is lighter than COALESCE
• With large datasets, performance may degrade if index optimization is affected
• Be careful to keep data types consistent (don’t mix numeric and string types)

7.6 Final takeaways

• In MySQL, use IFNULL to handle NULL properly
• You can process data without being impacted by NULL
• Understand the differences from COALESCE and NVL and use the right function
• When migrating between databases, pay attention to differences in NULL-handling functions