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:

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

Parameters

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

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

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

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.

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

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.

Output:

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

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