The MySQL IFNULL() function allows you to replace a NULL value with another specified value in your query result set.
The syntax of the IFNULL() function is as shown below:
IFNULL(expression, value);
There are two parameters required by the IFNULL() function for it to work properly: the expression and the value parameters.
The IFNULL() function will check on the expression argument to see if its value is NULL or not. When the expression argument equals NULL, then the function will return the value you specified as its second argument.
To see how the function works, let’s create a SELECT statement that uses the IFNULL() function as follows:
SELECT IFNULL(NULL, "Nathan"), IFNULL(0, "Nathan"), IFNULL("", "Nathan");
The query above will return the following result set:
+------------------------+---------------------+----------------------+
| IFNULL(NULL, "Nathan") | IFNULL(0, "Nathan") | IFNULL("", "Nathan") |
+------------------------+---------------------+----------------------+
| Nathan | 0 | |
+------------------------+---------------------+----------------------+
1 row in set (0.00 sec)
As you can see, the function only returns the string value "Nathan" when the first argument is exactly NULL.
When you pass a falsy value such as 0 or an empty string as the first argument, the function returns the first argument.
The function accepts any type of literal value or expression as the second argument. You can even pass another field name as the second argument.
Here’s an example of replacing a NULL value with an integer value:
mysql> SELECT IFNULL(NULL, 20);
+------------------+
| IFNULL(NULL, 20) |
+------------------+
| 20 |
+------------------+
1 row in set (0.00 sec)
The query result above replaces the NULL value with 20.
And that’s all the function does. You can use this function when you want to replace NULL values in your table with another value to help make sense of the result set.
For example, suppose you have a table of students with the following data. Notice how the first and the last row has the NULL value on the subject field:
+----+---------+---------+-------+--------+
| id | name | subject | score | gender |
+----+---------+---------+-------+--------+
| 1 | Mark | NULL | 7 | male |
| 2 | Natalia | Math | 8 | female |
| 3 | Gary | Math | 6 | male |
| 4 | Joe | English | 8 | male |
| 5 | Sarah | Math | 6 | female |
| 6 | Peter | English | 6 | male |
| 7 | Nathan | NULL | 8 | male |
+----+---------+---------+-------+--------+
The students table above keeps the score of an exam taken by the students, but the first and the last row don’t have the subject information.
This makes it confusing because we don’t know if the score is for Math or English subject.
To replace the NULL values in the subject field, you can use the IFNULL function and pass the subject field as the first argument to the function.
The following MySQL statement:
SELECT id, name, IFNULL(subject, "English") AS subject, score
FROM students;
Will return the following output:
+----+---------+---------+-------+
| id | name | subject | score |
+----+---------+---------+-------+
| 1 | Mark | English | 7 |
| 2 | Natalia | Math | 8 |
| 3 | Gary | Math | 6 |
| 4 | Joe | English | 8 |
| 5 | Sarah | Math | 6 |
| 6 | Peter | English | 6 |
| 7 | Nathan | English | 8 |
+----+---------+---------+-------+
Keep in mind that the NULL values in the result set will be replaced with "English" only for the query result. The real value in the table column won’t be replaced.
Now you’ve learned how the IFNULL() function works. Nice work!
