{"id":2356,"date":"2018-04-05T13:59:15","date_gmt":"2018-04-05T08:29:15","guid":{"rendered":"https:\/\/pheonixsolutions.com\/blog\/?p=2356"},"modified":"2026-09-21T16:52:53","modified_gmt":"2026-09-21T11:22:53","slug":"mysql-replace-null-values-with-empty-string","status":"publish","type":"post","link":"https:\/\/pheonixsolutions.com\/blog\/mysql-replace-null-values-with-empty-string\/","title":{"rendered":"How to Replace NULL Values with an Empty String in MySQL"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><strong>Post Date:<\/strong> April 5, 2018<br><strong>Last Updated:<\/strong> September 21, 2026<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">When working with MySQL databases, some columns may contain <code>NULL<\/code> 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 (<code>''<\/code>) instead of <code>NULL<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">MySQL provides the <code><strong>IFNULL()<\/strong><\/code> function to handle this situation.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In this guide, we will create two sample tables and use <code>IFNULL()<\/code> with a <code>LEFT JOIN<\/code> to return an empty string when a related value is <code>NULL<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Prerequisites<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before following this example, make sure you have:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>MySQL installed and running.<\/li>\n\n\n\n<li>Access to a MySQL client such as the MySQL command line or a database management tool.<\/li>\n\n\n\n<li>Permission to create tables and execute <code>SELECT<\/code> queries.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Implementation<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1: Create the Users Table<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Create a sample <code>users<\/code> table:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">CREATE TABLE `users` (\n    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n    `username` varchar(16) NOT NULL,\n    `name` varchar(40) DEFAULT NULL,\n    `last_name` varchar(60) DEFAULT NULL,\n    PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Insert a sample user:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">INSERT INTO `users` (`username`, `name`, `last_name`)\nVALUES ('xxx', 'xyz', 'abc');<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 2: Create the Billings Table<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Create a second table to store billing information:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">CREATE TABLE `billings` (\n    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n    `user_id` int(11) NOT NULL,\n    `address` varchar(16) NOT NULL,\n    PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Insert sample billing information:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">INSERT INTO `billings` (`user_id`, `address`)\nVALUES (1, '');<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The <code>users<\/code> table contains user information, while the <code>billings<\/code> table contains the corresponding billing address.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 3: Use IFNULL() to Replace NULL Values<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">MySQL provides the <code>IFNULL()<\/code> function to return an alternative value when an expression evaluates to <code>NULL<\/code>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Syntax<\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">IFNULL(expression, replacement_value)<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">For example:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">IFNULL(NULL, '') AS value<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The result will be an empty string instead of <code>NULL<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 4: Use IFNULL() with a LEFT JOIN<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">We can use <code>IFNULL()<\/code> together with a <code>LEFT JOIN<\/code> to return an empty string when no matching billing record exists.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT\n    u.*,\n    IFNULL(b.address, '') AS address\nFROM `users` AS u\nLEFT JOIN `billings` AS b\n    ON b.user_id = u.id\nORDER BY u.id;<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">How the Query Works<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The query performs the following operations:<\/p>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li>Retrieves records from the <code>users<\/code> table.<\/li>\n\n\n\n<li>Uses a <code>LEFT JOIN<\/code> to find matching records in the <code>billings<\/code> table.<\/li>\n\n\n\n<li>Matches records using <code>user_id<\/code>.<\/li>\n\n\n\n<li>Uses <code>IFNULL()<\/code> to check the billing address.<\/li>\n\n\n\n<li>Returns an empty string when <code>b.address<\/code> is <code>NULL<\/code>.<\/li>\n\n\n\n<li>Returns the actual address when a value exists.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">For example, if there is no matching billing record:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">NULL<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">will be returned as:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">''<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">IFNULL() vs COALESCE()<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Another commonly used MySQL function is <code>COALESCE()<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For a single fallback value, these produce equivalent results:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">IFNULL(b.address, '') AS address<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">and:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">COALESCE(b.address, '') AS address<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><code>IFNULL()<\/code> accepts two arguments, while <code>COALESCE()<\/code> can evaluate multiple expressions and return the first non-<code>NULL<\/code> value.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For example:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">COALESCE(b.address, u.name, '') AS value<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This returns:<\/p>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><code>b.address<\/code> if it is not <code>NULL<\/code>.<\/li>\n\n\n\n<li>Otherwise <code>u.name<\/code> if it is not <code>NULL<\/code>.<\/li>\n\n\n\n<li>Otherwise an empty string.<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\">Note: NULL and Empty String Are Different<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A <code>NULL<\/code> value and an empty string are not the same in MySQL.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>NULL<\/code> generally represents the absence of a value, while:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">''<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">is an actual string containing zero characters.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For example:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT IFNULL(NULL, '') AS value;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">returns an empty string.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is particularly useful when preparing database results for applications that need a consistent string value instead of <code>NULL<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">MySQL&#8217;s <code>IFNULL()<\/code> function provides a simple way to replace <code>NULL<\/code> values with an empty string when retrieving data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The basic syntax is:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">IFNULL(column_name, '') AS column_name<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">For example:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT\n    u.*,\n    IFNULL(b.address, '') AS address\nFROM users AS u\nLEFT JOIN billings AS b\n    ON b.user_id = u.id;<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This allows the query to return an empty string whenever the joined column contains <code>NULL<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Frequently Asked Questions<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1. How do I replace NULL with an empty string in MySQL?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Use the <code>IFNULL()<\/code> function:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT IFNULL(column_name, '') AS column_name\nFROM table_name;<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">2. What is the difference between NULL and an empty string?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><code>NULL<\/code> represents the absence of a value, while <code>''<\/code> is an actual empty string. They are treated differently in SQL comparisons and functions.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">3. Can I use IFNULL() with a LEFT JOIN?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Yes. This is a common use case:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT\n    u.*,\n    IFNULL(b.address, '') AS address\nFROM users AS u\nLEFT JOIN billings AS b\n    ON b.user_id = u.id;<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">4. Can I use COALESCE() instead of IFNULL()?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Yes. For a single fallback value, both can be used:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">COALESCE(b.address, '')<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><code>COALESCE()<\/code> is particularly useful when you need to check multiple possible values.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">5. Does IFNULL() permanently change the database value?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">No. Using <code>IFNULL()<\/code> in a <code>SELECT<\/code> query only changes the value returned by that query. It does not modify the stored value in the database.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Related Articles<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/pheonixsolutions.com\/blog\/how-to-enable-the-slow-query-log-mysql-in-linux\/\">How to enable the slow query log \u2013 Mysql in Linux<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/pheonixsolutions.com\/blog\/configuring-mysql-to-listen-on-a-private-ip-address\/\">Configuring MySQL to Listen on a Private IP Address<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/pheonixsolutions.com\/blog\/how-to-change-mysql-user-authentication-plugin-for-password\/\">How to Change MySQL User Authentication Plugin for Password?<\/a><\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Talk to our experts<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Have a technology challenge or looking for the right solution for your business? Our team can help you with <strong>cloud, DevOps, development, infrastructure, design, and more<\/strong>. Feel free to reach out to our experts <a href=\"https:\/\/pheonixsolutions.com\/contact\">here<\/a>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Post Date: April 5, 2018Last Updated: September 21, 2026 Introduction When working with MySQL databases, some columns may contain NULL [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[1022],"tags":[179],"class_list":["post-2356","post","type-post","status-publish","format-standard","hentry","category-web-architecture","tag-mysql-2","psol-cat-web-architecture"],"jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/phn2x7-C0","jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/2356","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=2356"}],"version-history":[{"count":1,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/2356\/revisions"}],"predecessor-version":[{"id":11866,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/2356\/revisions\/11866"}],"wp:attachment":[{"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=2356"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=2356"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pheonixsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=2356"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}