MySQL IFNULL()

This section helps you to learn about the MySQL IFNULL() function. The IFNULL function is a part of the MySQL control flow function used for handling NULL values.

The IFNULL function accepts two expressions, and if the first expression is not null, it returns the first arguments. If the first expression is null, it returns the second argument. This function returns either string or numeric value, depending on the context where it is used.

Syntax

We can use the IFNULL function with the following syntax:

snippet

IFNULL (Expression1, Expression2)

It returns expression1 when the expression1 is not null. Otherwise, it will return expression2.

Parameters

Parameter	Requirement	Descriptions
Expression 1	Required	This expression is used to check whether it is NULL or not.
Expression 2	Required	It will return when the expression 1 is NULL.

MySQL version support

The IFNULL function can support the following MySQL versions:

MySQL 8.0
MySQL 5.7
MySQL 5.6
MySQL 5.5
MySQL 5.1
MySQL 5.0
MySQL 4.1
MySQL 4.0

Let us understand the MySQL IFNULL() function with the following examples. We can use the IFNULL function with the SELECT statement directly.

Example 1

snippet

SELECT IFNULL(0,5);

In the above function, the MySQL statement checks the first expression. If the first expression is not NULL, it will return the first expression, which is zero.

Output:

Output

Example 2

snippet

SELECT IFNULL("Hello", "rookienerd");

The above MySQL statement checks the first expression. If the first expression is not NULL, it will return the first expression, which is 'Hello' value.

Output:

Output

Hello

Example 3

snippet

SELECT IFNULL(NULL,5);

The following MySQL statement checks the first expression. If the first expression is not NULL, it will return the first expression. Otherwise, it will return the second expression, which is five (5).

Output:

Output

Example 4

Here, we are going to create a table 'student_contacts' and perform the IFNULL() function.

snippet

CREATE TABLE `student_contacts` (
  `studentid` int unsigned NOT NULL AUTO_INCREMENT,
  `contactname` varchar(45) NOT NULL,
  `cellphone` varchar(20) DEFAULT NULL,
  `homephone` varchar(20) DEFAULT NULL,
  );

Now, you need to insert data into a table. After inserting the values into the table, execute the following query.

snippet

SELECT 
    contactname, cellphone, homephone
FROM
    student_contacts;

It will display the output that contains all rows and columns. Here, we can see that some of the contacts have only a cell phone or home phone number.

In the above output, we will get all contacts name weather cell phone, and home phone number is available or not. So, in that case, the IFNULL() function plays an important role.

Now, run the following MySQL query. This statement returns the home phone number if the cell phone is NULL.

snippet

SELECT 
    contactname, IFNULL(cellphone, homephone) phone
FROM
    student_contact;

Output:

When the above MySQL statement runs successfully, it will give the following output.

Note

Note: You should avoid the use of the IFNULL() function in the WHERE clause because this function reduces the performance of the query.