MySQL: IFNULL Function with Example

MySQL ifnull() function; Through this tutorial, we will learn how to use the MySQL IFNULL function with the help of examples.

If you want to check if a value is NULL or not, you can use IS NULL or IS NOT NULL with MySQL Joins, Logical operators, MySQL clauses.

MySQL IFNULL() function

In MySQL IFNULL (), we can use two expressions(agruments) and if the first expression given is not NULL, then it gives the first expression. Otherwise, it returns the second expression.

Syntax

The syntax of the IFNULL function is:

IFNULL(expression_1,expression_2);

If Expression_1 is not NULL then it will return from Expression_1, otherwise it gives Expression 2.

Example of MySQL IFNULL

Let’s see example of IFNULL function.

First, we need to create a new table named users using the following statement :

CREATE TABLE IF NOT EXISTS users (
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(20) NOT NULL,
     businessphone VARCHAR(15),
     homephone VARCHAR(15)
 );

Next, we need to insert data into the users table using the following query :

 INSERT INTO users(name,homephone,businessphone)
 VALUES('Tommy Hill','(541) 754-3009',NULL),
       ('John Smith',NULL,'(541) 754-3110'),
       ('Mark Greenspan','(541) 754-3010','(541) 754-3011'),
       ('Kelvin Row',NULL,'(541) 754-3111');

If you want to fetch some users have only home phone or business phone. You can use the following query for that:

SELECT
     name, businessphone, homephone
 FROM
     users;

   # Result of the above Query

   +------------+-----------+----------+-----------+-------------------
   | id        | name           | homephone       | businessphone   |
   +------------+-----------+----------+-----------+-------------------
   | 5         | Tommy Hill     | NULL            |  (541) 754-3009 |
   | 6         | John Smith     | (541) 754-3110  |  NULL           |
   | 10        | Mark Greenspan | (541) 754-3010  |  (541) 754-3111 |
   | 11        | Kelvin Row     | (541) 754-3111  |  NULL           |
   +------------+-----------+----------+-----------+-------------------

If we can obtain contact details of users like home and business phone numbers. If there is no business phone in any row, then just be a home phone number. If someone has a home phone number but not a business phone number

In the above situation, we can use the MySQL IFNULL function. IFNULL function returns a home phone if the business phone is NULL.

SELECT
     name, IFNULL(businessphone, homephone) phone
 FROM
     users;

   #The above query return this result from users table

   +------------+-----------+----------+-----------
   | id        | name           | phone          |
   +------------+-----------+----------+-----------
   | 5         | Tommy Hill     | (541) 754-3009 |
   | 6         | John Smith     | (541) 754-3110 |
   | 10        | Mark Greenspan | (541) 754-3111 |
   | 11        | Kelvin Row     | (541) 754-3111 |
   +------------+-----------+----------+-----------

When we fetch the business and home phone number of users table.

• If the users business phone is not exist in database table, in that case it return home number.
• If the users home phone is not exist in database table, in that case it return business number.

Conclusion

In this tutorial, we have learn how to use MySQL IFNULL function.

Recommended MySQL Tutorials