How to Use the MySQL IFNULL() Function
In this tutorial, you will learn how to use the MySQL IFNULL() function that returns the specified value if the expression is NULL; otherwise returns that expression.
If you want to compare two expressions and return NULL if they are equal, use the NULLIF() function.
IFNULL() Syntax
The syntax of the IFNULL() function is as follow:
IFNULL (expression_1, expression_2)
Examples
Example 1
Let's take a look at the following statement:
SELECT IFNULL ("Hello!", "NULL") AS Message;
Output:
As you can see, IFNULL checks whether "Hello!" is NULL or not; if "Hello!" is NULL, it returns null; otherwise returns "Hello!". The string "Hello!" is not null, so 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, suppose we have a table named "customers." This table consists of three 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;
IFNULL() checks the group column and returns "The customer group is no set!" if the value is null; otherwise returns the value of the group column.
The result set from the above statement is as follows:
| customer_id | customer_name | group |
|---|---|---|
| 1 | James | Retail Customers |
| 2 | Roland | The customer group is not set! |
| 3 | John | Online Customers |
IFNULL() returns the value of the group column for the first and third customers because the group is not NULL, and for the second customer, the group is NULL, so IFNULL() returns "The customer group is not set!".
In this tutorial, you have learned how to use the MySQL IFNULL() function, which returns the specified value if the expression is NULL; otherwise returns that expression.