In MySQL, sometimes you don’t want NULL values to be returned as NULL. Sometimes you want NULL values to be returned with a different value, such as “N/A”, “Not Applicable”, “None”, or even the empty string “”.

Fortunately there are several ways to do this in MySQL.

Here are four:

• The IFNULL() function
• The COALESCE() function
• The IF() function combined with the IS NULL (or IS NOT NULL) operator
• The CASE expression combined with the IS NULL (or IS NOT NULL) operator

Examples of these options are below.

Sample Data

First, let’s grab some sample data:

USE Solutions;
SELECT TaskCode
From Tasks;

Result:

+----------+
| TaskCode |
+----------+
| gar123   |
| NULL     |
| NULL     |
| dog456   |
| NULL     |
| cat789   |
+----------+

So we have three NULL values and three non-NULL values.

The IFNULL() Function

Given its name, this is probably the most obvious option for replacing NULL values in MySQL. This function is basically the equivalent of ISNULL() in SQL Server.

The IFNULL() function allows you to provide two arguments. The first argument is returned only if it is not NULL. If it is NULL, then the second argument is returned instead.

Here’s an example of using IFNULL() against our sample data set:

SELECT IFNULL(TaskCode, 'N/A') AS Result 
FROM Tasks;

Result:

+--------+
| Result |
+--------+
| gar123 |
| N/A    |
| N/A    |
| dog456 |
| N/A    |
| cat789 |
+--------+

Here, we simply replaced NULL values with N/A.

The COALESCE() Function

This function is similar to the IFNULL() function, but slightly different. This function adheres to the ANSI  SQL standard, and it is widely deployed across various RDBMSs .

The way it works is, you provide as many arguments as you need. COALESCE() will then return the first non-NULL value in the list, or NULL if there are no non-NULL values.

Like this:

SELECT COALESCE(TaskCode, 'N/A') AS Result 
FROM Tasks;

Result:

+--------+
| Result |
+--------+
| gar123 |
| N/A    |
| N/A    |
| dog456 |
| N/A    |
| cat789 |
+--------+

So we get exactly the same result as previously.

However, the difference with this function is that, as mentioned, you can provide a list of arguments. The COALESCE() function will take whichever is the first non-NULL value.

So for example, we could add NULL as the first argument and place None before N/A and look what happens:

SELECT COALESCE(NULL, TaskCode, 'None', 'N/A') AS Result 
FROM Tasks;

Result:

+--------+
| Result |
+--------+
| gar123 |
| None   |
| None   |
| dog456 |
| None   |
| cat789 |
+--------+

It skipped the first NULL as expected, then it skipped any NULL values in the TaskCode column, before settling on None.  The N/A value didn’t get used in this case because None came first and it’s a non-NULL value.

The IF() Function Combined with IS NULL/IS NOT NULL

The IS NULL and IS NOT NULL operators allow you to test for NULL values, and present a different value depending on the outcome.

We can use these operators inside the IF() function, so that non-NULL values are returned, and NULL values are replaced with a value of our choosing.

Example:

SELECT IF(TaskCode IS NOT NULL, TaskCode, 'N/A') AS Result 
FROM Tasks;

Result:

+--------+
| Result |
+--------+
| gar123 |
| N/A    |
| N/A    |
| dog456 |
| N/A    |
| cat789 |
+--------+

So the same result as with the IFNULL() and COALESCE() functions.

And of course, we could swap IS NOT NULL with IS NULL. If we do that, we would need to swap the subsequent arguments too:

SELECT IF(TaskCode IS NULL, 'N/A', TaskCode) AS Result 
FROM Tasks;

The CASE Expression Combined with IS NULL/IS NOT NULL

Another way to do it is to use the CASE expression:

SELECT 
    CASE 
        WHEN TaskCode IS NOT NULL THEN TaskCode 
        ELSE 'N/A' 
    END AS Result
FROM Tasks;

Result:

+--------+
| Result |
+--------+
| gar123 |
| N/A    |
| N/A    |
| dog456 |
| N/A    |
| cat789 |
+--------+

As with the previous example, this could be rewritten to use IS NULL instead of IS NOT NULL:

SELECT 
    CASE 
        WHEN TaskCode IS NULL THEN 'N/A' 
        ELSE TaskCode 
    END AS Result
FROM Tasks;