MySQL IFNULL

Syntax of MySQL IFNULL() Function

The syntax of the MySQL IFNULL() function is as follows:

We use the function name namely IFNULL with parenthesis. Inside the parenthesis, there are two parameters namely expression and alternate_value. Let us see what are these parameters and how are they used in the following section.

Parameters of MySQL IFNULL() Function

There are two parameters of the MySQL IFNULL() function. The first parameter is Expression and the second parameter is alternate_value.

• Expression:
  It is defined as the value that is tested if it is null or not. It is a required parameter.
• Alternate_value:
  It is defined as the value that is returned as the output of the MySQL IFNULL() if the expression parameter is null. It is also a required parameter.

MySQL IFNULL() function can be used with most of the version of MySQL but not every version supports this function. Only the versions from MySQL 4.0 and afterward supports the MySQL IFNULL() function. Here is a list of the MySQL versions that supports the MySQL IFNULL() function.

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

Return Value of MySQL IFNULL() Function

The return value of the MySQL IFNULL() function is a string value or numeric value that we have defined in the parameters of the function. We get the return value according to the parameter that we describe in the syntax of the function. For example, if the expression is not null, the value in it is returned as output. And if the expression is null, we get the value in the Alternate_value as the return value of the function.

Exceptions of MySQL IFNULL() Function

The MySQL IFNULL() function is used to provide the result after making the comparison between the two parameters provided inside the parenthesis of the function. This function returns only a string value or a numeric value as the output. If you want to get any boolean value (TRUE or FALSE), then you can use the MySQL IF() function. Also, the MySQL IFNULL() function is only supported in MySQL version 4.0 and later versions.

How does the MySQL IFNULL() Function Work?

As we know the MySQL IFNULL() function is used to return some value after making the comparison between the provided parameters in the function. The function first checks the first parameter, If the first parameter is NOT NULL, it returns whatever the value of that particular parameter. And if the first parameter is NULL, the function returns the second parameter as the output of the function. For example,

If the first_parameter is NOT NULL, the function returns the value of that particular parameter, and if the first_parameter is NULL, then the function returns the value of the second_parameter as the output of the function.

Examples

Till now, you have learned what is MySQL IFNULL() function is and what are the various aspects of this function. Now let us discuss various examples of the MySQL IFNULL() function. All the examples are discussed using output and explanation for a better understanding.

Example 1.

Output:

Explanation:
In the above example, we are checking whether the first parameter provided with the function is null or not. We are using the IFNULL function with a SELECT clause. Inside the parenthesis, there we have assigned 0 as the first parameter and 10 as the second parameter. When we execute the function, we get 0 as the output because the first parameter has some value that is not null. So, the function returns the first parameter as the output of the function.

Example 2

Output:

Explanation:
In the above example, we are checking whether the first parameter provided with the function is null or not. We are using the IFNULL function with a SELECT clause. Inside the parenthesis, there we have assigned Hey as the expression parameter and It is Null as the Alternate_value parameter. When we execute the function, we get Hey as the output because the first parameter has some value that is NOT NULL. So, the function returns the first parameter as the output of the function.

Example 3

Output:

Explanation:
In the above example, we are checking whether the first parameter provided with the function is null or not. We are using the IFNULL function with a SELECT clause. Inside the parenthesis, there we have assigned NULL as the expression parameter and 5 as the Alternate_value parameter. When we execute the function, we will get the output because the first parameter has some value that is NULL. So, the function returns the second parameter after comparison, as the output of the MySQL IFNULL() function.

Example 4

In this example, we will see how the MySQL IFNULL() function works with tables. For doing this, first, we will create a table. To create the table namely contact_info, we will execute the following query:

Now we need to insert some data such as id, name, and contact details into the table. For doing this, we will use the query given below:

To see the table that we have created, we need to execute the following query:

Output:

Now, suppose there is a data table that has the contact information of different people. There are two types of contact numbers that are biz_phone and home_phone. Some people have both types of contact numbers and some of them have either of the contact number available. If we want to know the contact number of the home if any person's business contact is not available. Here comes the use of the MySQL IFNULL() function with tables. To fetch this kind of detail, we need to execute the command given below:

Output:

See Also

Please read about other functions like:

• CURDATE()
• TIME()
• TIMESTAMP()
• SELECT()
• COUNT()
• SUM()
• AVERAGE()
• MIN()
• MAX()