The Essential Guide To MySQL ISNULL Function

 The Essential Guide To 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 the 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)

Consider the following examples:

SELECT ISNULL(NULL); -- 1    
SELECT ISNULL(1);  -- 0
SELECT ISNULL(1 + NULL); -- 1;
SELECT ISNULL(1 / 0 ); -- 1;

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 the 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 a special date '0000-00-00' by using the following statement:

SELECT 
    *
FROM
    table_name
WHERE
    ISNULL(date);

Note that MySQL purposely implemented this feature to support ODBC applications because ODBC does not support a 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
);

Second, insert some data into the special_isnull table:

INSERT INTO special_isnull(start_date) 
VALUES('2000-01-01'),
      ('0000-00-00');

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

SELECT 
    *
FROM
    special_isnull
WHERE
    ISNULL(start_date); 

The query returned one row while you may be 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.