IFNULL

Introduction to MySQL IFNULL function

MySQL IFNULL function is one of the MySQL control flow functions that accepts two arguments and returns the first argument if it is not NULL. Otherwise, the IFNULLfunction returns the second argument.

IFNULL(expression_1, expression_2)

Breakdown:

• expression_1: The value or expression you want to check.
• expression_2: The fallback value that will be returned if expression_1 is NULL.

3. How it Works

• If expression_1 is NOT NULL, then IFNULL() returns expression_1.
• If expression_1 is NULL, then IFNULL() returns expression_2.

4. Example 1: Simple values

SELECT IFNULL(NULL, 'No Value') AS result;

Output:

No Value

SELECT IFNULL('Teslim', 'No Value') AS result;

Output:

Teslim

5. Example 2: With Table Data

Suppose we have a table employees:

Query:

SELECT
    name,
    IFNULL(bonus, 0) AS final_bonus
FROM employees;

Output:

👉 Here, the NULL in Mary’s bonus is replaced with 0.

6. Notes

• IFNULL() is not the same as IF().
• IFNULL() only checks for NULL.
• IF() can check for any condition (TRUE or FALSE).

Example difference:

-- IFNULL: Only handles NULL
SELECT IFNULL(NULL, 'Fallback');  -- returns 'Fallback'

-- IF: Handles conditions
SELECT IF(5 > 2, 'Yes', 'No');   -- returns 'Yes'

✅ In short: Use IFNULL() when you want to replace NULL with a default value in your queries.

Example 1

Let’s take a practical example of using the IFNULL function.

First, create a new table named contacts using the following statement:

CREATE TABLE IF NOT EXISTS contacts (
    contactid INT AUTO_INCREMENT PRIMARY KEY,
    contactname VARCHAR(20) NOT NULL,
    bizphone VARCHAR(15),
    homephone VARCHAR(15)
);

Each contact has a name, business phone and home phone.

Second, insert data into the contacts table:

INSERT INTO contacts(contactname,bizphone,homephone)
VALUES('John Doe','(541) 754-3009',NULL),
      ('Cindy Smith',NULL,'(541) 754-3110'),
      ('Sue Greenspan','(541) 754-3010','(541) 754-3011'),
      ('Lily Bush',NULL,'(541) 754-3111');

Some contacts have only home phone or business phone. To get all the contact name and phone from the contacts table, you use the following query:

SELECT 
    contactName, bizphone, homephone
FROM
    contacts;

contactName    bizphone        homephone       
-------------  --------------  --------------  
John Doe       (541) 754-3009  (null)          
Cindy Smith    (null)          (541) 754-3110  
Sue Greenspan  (541) 754-3010  (541) 754-3011  
Lily Bush      (null)          (541) 754-3111  

It would be nice if we can get the contact’s home phone if the contact’s business phone is not available. This is where the

This is where the IFNULL function comes to play. The IFNULL function returns the home phone if the business phone is NULL.

Third, use the following query to get the names and phones of all the contacts:

contactname    phone
-------------  --------------
John Doe       (541) 754-3009
Cindy Smith    (541) 754-3110
Sue Greenspan  (541) 754-3010
Lily Bush      (541) 754-3111

Notice that you should avoid using the IFNULL function in the WHERE clause, because it degrades the performance of the query. If you want to check if a value is

If you want to check if a value is NULL or not, you can use IS NULL or IS NOT NULL in the WHERE clause.

In this tutorial, we have introduced you to MySQL IFNULL function and shown you how to use the IFNULL function in the queries.