MySQL IFNULL() Function with Examples


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_idcustomer_namegroup
1JamesRetail Customers
2Roland 
3JohnOnline 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_idcustomer_namegroup
1JamesRetail Customers
2RolandThe customer group is not set!
3JohnOnline 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.

Leave a Reply

Your email address will not be published. Required fields are marked *