How to Use the MySQL IFNULL() Function
In this tutorial, we'll learn how to use the MySQL IFNULL() function. IFNULL() is a function to return the specified value when the expression in NULL. But if the expression is not NULL, it returns the expression.
IFNULL() Syntax
The syntax of the IFNULL() function is as follow:
IFNULL (expression_1, expression_2)
Examples
Example 1
Let's look at the statement below:
SELECT IFNULL ("Hello!", "NULL") AS Message;
Result:
As you can see, IFNULL checks if Hello! is NULL. If Hello! is NULL, it returns NULL, otherwise returns Hello!. Since the string Hello! is not NULL, IFNULL returns Hello!;
Example 2
The following statement returns 1000 because the first expression is NULL.
SELECT IFNULL (NULL, 1000) AS Message;
Example 3
For example, we have the following customers table. This table consists of 3 columns, customer_id, customer_name, and group. The group column can be NULL.
| customer_id | customer_name | group |
|---|---|---|
| 1 | James | Retail Customers |
| 2 | Roland | |
| 3 | John | Online Customers |
SELECT customer_id, customer_name, IFNULL(group, "The customer group is not set!") AS group
FROM customers;
As you can see, in this statement, IFNULL() checks the group column if the value is NULL, it returns The customer group is no set!, otherwise returns the value of the group column.
The result set from this statement as follows:
| customer_id | customer_name | group |
|---|---|---|
| 1 | James | Retail Customers |
| 2 | Roland | The customer group is not set! |
| 3 | John | Online Customers |
As we can see in this result table, in case of the 1st and 3rd customers, since the group is not NULL, IFNULL() returns the value of the group column. But, in case of the 2nd customer, since the group is NULL, IFNULL() returns The customer group is not set!.
In this tutorial, we've learned how to use the MySQL IFNULL() function. IFNULL() is used to return the specified value when the expression is NULL, otherwise returns the expression.