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
0

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
5

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.

MySQL IFNULL

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.

MySQL IFNULL
Note
Note: You should avoid the use of the IFNULL() function in the WHERE clause because this function reduces the performance of the query.
Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents +