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 function takes one argument and tests whether that argument is NULL or not. The ISNULLfunction returns 1 if the argument is NULL, otherwise, it returns 0.

The following illustrates the syntax of the ISNULL function:

1	ISNULL(expr)

Consider the following examples:

1 2 3 4

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 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:

1 2 3 4 5 6

SELECT     * FROM     table_name WHERE     ISNULL(date);

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:

1 2 3

CREATE TABLE special_isnull (     start_date DATE NOT NULL );

Second, insert some data into the special_isnull table:

1 2 3

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

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

1 2 3 4 5 6

SELECT     * FROM     special_isnull WHERE     ISNULL(start_date);

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.