A Comprehensive Look at MySQL IS NULL Operator?

Summary: in this tutorial, you will learn how to use the MySQL IS NULL operator to test whether a value is NULL or not.

Introduction to MySQL IS NULL operator

To test whether a value is NULL or not, you use the  IS NULL operator. The following show syntax of the IS NULL operator:

1	value IS NULL

If the value is NULL, the expression returns true. Otherwise, it returns false.

Note that MySQL does not have the built-in BOOLEAN type. It uses the TINYINT(1) to represent the BOOLEAN values i.e.,  true means 1 and false means 0.

Because the IS NULL is a comparison operator, you can use it anywhere that  an operator can be used e.g., in the SELECT or WHERE clause. See the follow example:

1 2 3

SELECT 1 IS NULL,  -- 0        0 IS NULL,  -- 0        NULL IS NULL; -- 1

To check if a value is not NULL, you use IS NOT NULL operator as follows:

1	value IS NOT NULL

This expression returns true (1) if the value is not NULL. Otherwise, it returns false (0). Consider the following example:

1 2 3

SELECT 1 IS NOT NULL, -- 1        0 IS NOT NULL, -- 1        NULL IS NOT NULL; -- 0

MySQL IS NULL examples

We will use the customers table in the sample database for the demonstration.

To get customers who do not have sales representative, you use the IS NULL operator as follows:

1 2 3 4 5 6 7 8 9

SELECT     customerName,     country,     salesrepemployeenumber FROM     customers WHERE     salesrepemployeenumber IS NULL ORDER BY customerName;

To get the customers who have a sales representative, you use the IS NOT NULL operator:

1 2 3 4 5 6 7 8 9

SELECT     customerName,     country,     salesrepemployeenumber FROM     customers WHERE     salesrepemployeenumber IS NOT NULL ORDER BY customerName;

MySQL IS NULL’s specialized features

To be compatible with ODBC programs, MySQL supports some specialized features of the IS NULLoperator.

1) If the DATE or DATETIME column that has NOT NULL constraint and contains the special date '0000-00-00', you can use the IS NULL operator to find such rows.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

CREATE TABLE IF NOT EXISTS projects (     id INT AUTO_INCREMENT,     title VARCHAR(255),     begin_date DATE NOT NULL,     complete_date DATE NOT NULL,     PRIMARY KEY(id) );   INSERT INTO projects(title,begin_date, complete_date) VALUES('New CRM','2020-01-01','0000-00-00'),       ('ERP Future','2020-01-01','0000-00-00'),       ('VR','2020-01-01','2030-01-01');     SELECT     * FROM     projects WHERE     complete_date IS NULL;

In this example, we created a new table named projects and insert some data into the table. The last query used IS NULL to get the rows whose values in the complete_date value is '0000-00-00'.

2) If the variable @@sql_auto_is_null is set to 1, you can get the value of a generated column after executing an INSERT statement by using the IS NULL operator. Note that by default the variable @@sql_auto_is_null is 0. See the following example.

First, set the variable @@sql_auto_is_null to 1.

1	SET @@sql_auto_is_null = 1;

Second, insert a new row into the projects table:

1 2	INSERT INTO projects(title,begin_date, complete_date) VALUES('MRP III','2010-01-01','2020-12-31');

Third, use the IS NULL operator to get the generated value of the id column:

1 2 3 4 5 6

SELECT     id FROM     projects WHERE     id IS NULL;

MySQL IS NULL Optimization

MySQL performs the same optimization for the IS NULL operator in the same way that it does for the equal (=) operator.

For example, MySQL uses the index when it searches for NULL with the IS NULL operator as shown in the following query:

1 2 3 4 5 6 7

SELECT     customerNumber,     salesRepEmployeeNumber FROM     customers WHERE     salesRepEmployeeNumber IS NULL;

See the EXPLAIN of the query:

1 2 3 4 5 6 7

EXPLAIN SELECT     customerNumber,     salesRepEmployeeNumber FROM     customers WHERE     salesRepEmployeeNumber IS NULL;

MySQL can also optimize for the combination col = value OR col IS NULL. See the following example:

1 2 3 4 5 6 7 8

EXPLAIN SELECT     customerNumber,     salesRepEmployeeNumber FROM     customers WHERE     salesRepEmployeeNumber = 1370 OR     salesRepEmployeeNumber IS NULL;

In this example, the EXPLAIN shows ref_or_null when the optimization is applied.

If you have a key that is a combination of columns, MySQL can perform optimization for any key part. Suppose there is an index on columns k1 and k2 of the table t1, the following query is optimized:

1 2 3 4 5 6

SELECT     * FROM     t1 WHERE     k1 IS NULL;

In this tutorial, you have learned how to use MySQL IS NULL operator to test whether a value is NULL or not.