MySQL IFNULL

Summary: in this tutorial, you will learn about the MySQL IFNULL function, which is a very handy control flow function to handle NULL values.

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 IFNULL function returns the second argument.

The two arguments can be literal values or expressions.

The following illustrates the syntax of the IFNULL function:

IFNULL(expression_1,expression_2);Code language: SQL (Structured Query Language) (sql)

The IFNULL function returns expression_1 if expression_1 is not NULL ; otherwise, it returns expression_2. The

The IFNULL function returns a string or a numeric based on the context where it is used.

If you want to return a value based on TRUE or FALSE condition other than NULL, you should use the IF function.

MySQL IFNULL function examples

See the following IFNULL function examples:

SELECT IFNULL(1,0); -- returns 1
Code language: SQL (Structured Query Language) (sql)

Try It Out

SELECT IFNULL('',1); -- returns ''
Code language: SQL (Structured Query Language) (sql)

Try It Out

SELECT IFNULL(NULL,'IFNULL function'); -- returns IFNULL functionCode language: SQL (Structured Query Language) (sql)

Try It Out

How it works.

• IFNULL(1,0) returns 1 because 1 is not NULL .
• IFNULL(' ',1) returns ' ' because the ' ' string is not NULL .
• IFNULL(NULL,'IFNULL function') returns IFNULL function string because the first argument is NULL.

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)
);Code language: SQL (Structured Query Language) (sql)

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');Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Try It Out

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:

SELECT 
    contactname, IFNULL(bizphone, homephone) phone
FROM
    contacts;Code language: SQL (Structured Query Language) (sql)

Try It Out

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.