MySQL ISNULL Function

Summary: This tutorial introduces you to the MySQL ISNULL function and how to use it to handle NULL values.

Introduction to MySQL ISNULL function

The ISNULL function takes one argument and tests whether that argument is NULL or not. The ISNULL function returns 1 if the argument is NULL, otherwise, it returns 0.

The following illustrates the syntax of the ISNULL function:

ISNULL(expr)
Code language: SQL (Structured Query Language) (sql)

Consider the following examples:

SELECT ISNULL(NULL); -- 1    
SELECT ISNULL(1);  -- 0
SELECT ISNULL(1 + NULL); -- 1;
SELECT ISNULL(1 / 0 ); -- 1;
Code language: SQL (Structured Query Language) (sql)

Notice that if you are trying to find the MySQL alternative to Microsoft SQL Server’s ISNULL function, you should use MySQL’s IFNULL function instead. Because the ISNULL function is MySQL is different from the Microsoft SQL Server’s ISNULL function.

MySQL ISNULL function & IS NULL operator

The ISNULL function shares some behaviors with the IS NULL operator. For example, if you have a DATE column declared as NOT NULL, you can find the special date '0000-00-00' by using the following statement:

SELECT 
    *
FROM
    table_name
WHERE
    ISNULL(date);
Code language: SQL (Structured Query Language) (sql)

Note that MySQL purposely implemented this feature to support ODBC applications because ODBC does not support special date value '0000-00-00'.

Let’s take a look at an example.

First, create a new table named special_isnull as follows:

CREATE TABLE special_isnull (
    start_date DATE NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert some data into the special_isnull table:

INSERT INTO special_isnull(start_date) 
VALUES('2000-01-01'),
      ('0000-00-00');Code language: SQL (Structured Query Language) (sql)

Third, query data from the special_isnull table using the ISNULLfunction:

SELECT 
    *
FROM
    special_isnull
WHERE
    ISNULL(start_date); Code language: SQL (Structured Query Language) (sql)

The query returned one row while you may expected it will return an empty result set.

When you want to negate the IS NULL operator, you use the NOT operator i.e., IS NOT NULL. However, for the ISNULL function, you use !ISNULL.

In this tutorial, you have learned how to use the MySQL ISNULL function and its special behavior to handle NULL values.