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:

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

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:

0

Example 2

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

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:

Hello

Example 3

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:

5

Example 4

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

 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.

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.

SELECT   

    contactname, IFNULL(cellphone, homephone) phone  

FROM  

    student_contact;  

Output:

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