How to Replace NULL Values with an Empty String in MySQL

Post Date: April 5, 2018
Last Updated: September 21, 2026

Introduction

When working with MySQL databases, some columns may contain NULL values when data is not available. Depending on how the query results are consumed by an application, you may want to return an empty string ('') instead of NULL.

MySQL provides the IFNULL() function to handle this situation.

In this guide, we will create two sample tables and use IFNULL() with a LEFT JOIN to return an empty string when a related value is NULL.

Prerequisites

Before following this example, make sure you have:

• MySQL installed and running.
• Access to a MySQL client such as the MySQL command line or a database management tool.
• Permission to create tables and execute SELECT queries.

Implementation

Step 1: Create the Users Table

Create a sample users table:

CREATE TABLE `users` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `username` varchar(16) NOT NULL,
    `name` varchar(40) DEFAULT NULL,
    `last_name` varchar(60) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert a sample user:

INSERT INTO `users` (`username`, `name`, `last_name`)
VALUES ('xxx', 'xyz', 'abc');

Step 2: Create the Billings Table

Create a second table to store billing information:

CREATE TABLE `billings` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `address` varchar(16) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert sample billing information:

INSERT INTO `billings` (`user_id`, `address`)
VALUES (1, '');

The users table contains user information, while the billings table contains the corresponding billing address.

Step 3: Use IFNULL() to Replace NULL Values

MySQL provides the IFNULL() function to return an alternative value when an expression evaluates to NULL.

Syntax

IFNULL(expression, replacement_value)

For example:

IFNULL(NULL, '') AS value

The result will be an empty string instead of NULL.

Step 4: Use IFNULL() with a LEFT JOIN

We can use IFNULL() together with a LEFT JOIN to return an empty string when no matching billing record exists.

SELECT
    u.*,
    IFNULL(b.address, '') AS address
FROM `users` AS u
LEFT JOIN `billings` AS b
    ON b.user_id = u.id
ORDER BY u.id;

How the Query Works

The query performs the following operations:

1. Retrieves records from the users table.
2. Uses a LEFT JOIN to find matching records in the billings table.
3. Matches records using user_id.
4. Uses IFNULL() to check the billing address.
5. Returns an empty string when b.address is NULL.
6. Returns the actual address when a value exists.

For example, if there is no matching billing record:

NULL

will be returned as:

''

IFNULL() vs COALESCE()

Another commonly used MySQL function is COALESCE().

For a single fallback value, these produce equivalent results:

IFNULL(b.address, '') AS address

and:

COALESCE(b.address, '') AS address

IFNULL() accepts two arguments, while COALESCE() can evaluate multiple expressions and return the first non-NULL value.

For example:

COALESCE(b.address, u.name, '') AS value

This returns:

1. b.address if it is not NULL.
2. Otherwise u.name if it is not NULL.
3. Otherwise an empty string.

Note: NULL and Empty String Are Different

A NULL value and an empty string are not the same in MySQL.

NULL generally represents the absence of a value, while:

''

is an actual string containing zero characters.

For example:

SELECT IFNULL(NULL, '') AS value;

returns an empty string.

This is particularly useful when preparing database results for applications that need a consistent string value instead of NULL.

Conclusion

MySQL’s IFNULL() function provides a simple way to replace NULL values with an empty string when retrieving data.

The basic syntax is:

IFNULL(column_name, '') AS column_name

For example:

SELECT
    u.*,
    IFNULL(b.address, '') AS address
FROM users AS u
LEFT JOIN billings AS b
    ON b.user_id = u.id;

This allows the query to return an empty string whenever the joined column contains NULL.

Frequently Asked Questions

1. How do I replace NULL with an empty string in MySQL?

Use the IFNULL() function:

SELECT IFNULL(column_name, '') AS column_name
FROM table_name;

2. What is the difference between NULL and an empty string?

NULL represents the absence of a value, while '' is an actual empty string. They are treated differently in SQL comparisons and functions.

3. Can I use IFNULL() with a LEFT JOIN?

Yes. This is a common use case:

SELECT
    u.*,
    IFNULL(b.address, '') AS address
FROM users AS u
LEFT JOIN billings AS b
    ON b.user_id = u.id;

4. Can I use COALESCE() instead of IFNULL()?

Yes. For a single fallback value, both can be used:

COALESCE(b.address, '')

COALESCE() is particularly useful when you need to check multiple possible values.

5. Does IFNULL() permanently change the database value?

No. Using IFNULL() in a SELECT query only changes the value returned by that query. It does not modify the stored value in the database.

Related Articles

• How to enable the slow query log – Mysql in Linux
• Configuring MySQL to Listen on a Private IP Address
• How to Change MySQL User Authentication Plugin for Password?

Talk to our experts

Have a technology challenge or looking for the right solution for your business? Our team can help you with cloud, DevOps, development, infrastructure, design, and more. Feel free to reach out to our experts here.