MySQL IFNULL() Function with Examples

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


See also:
MySQL LIKE Operator Pattern Matching and Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function | Concatenate Strings in MySQL

Leave a Comment