MySQL: IFNULL Function with Example

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.


The syntax of the IFNULL function is:


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 :

     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:

     name, businessphone, homephone
   # 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.

     name, IFNULL(businessphone, homephone) phone
   #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.


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

Recommended MySQL Tutorials


My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *