MySQL IFNULL Function with Example

MySQL ifnull() function; Through this tutorial, i am going to show you how to use MySQL IFNULL function with the help of examples.

MySQL IFNULL() function

The IFNULL() function returns a specified value if the expression is NULL. If the expression is NOT NULL, this function returns the expression

Syntax of MySQL IFNULL() function

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 1 – MySQL IFNULL() function

Let’s see example of IFNULL function.

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, you 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 |
   +------------+-----------+----------+-----------

There are two condition, if you want to fetch the business and home phone number of users table; as follows:

  • 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.

Leave a Comment