MySQL IS NULL

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.

Here’s the basic syntax of the IS NULL operator:

value IS NULLCode language: SQL (Structured Query Language) (sql)

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

Note that MySQL does not have a 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 following example:

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

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

value IS NOT NULLCode language: SQL (Structured Query Language) (sql)

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

Consider the following example:

SELECT 1 IS NOT NULL, -- 1 0 IS NOT NULL, -- 1 NULL IS NOT NULL; -- 0Code language: SQL (Structured Query Language) (sql)

MySQL IS NULL examples

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

The following query uses the IS NULL operator to find customers who do not have a sales representative:

SELECT customerName, country, salesrepemployeenumber FROM customers WHERE salesrepemployeenumber IS NULL ORDER BY customerName;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------------------+--------------+------------------------+ | customerName | country | salesrepemployeenumber | +--------------------------------+--------------+------------------------+ | ANG Resellers | Spain | NULL | | Anton Designs, Ltd. | Spain | NULL | | Asian Shopping Network, Co | Singapore | NULL | | Asian Treasures, Inc. | Ireland | NULL | ...Code language: PHP (php)

This example uses the IS NOT NULL operator to get the customers who have a sales representative:

SELECT customerName, country, salesrepemployeenumber FROM customers WHERE salesrepemployeenumber IS NOT NULL ORDER BY customerName;Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------------------+-------------+------------------------+ | customerName | country | salesrepemployeenumber | +------------------------------------+-------------+------------------------+ | Alpha Cognac | France | 1370 | | American Souvenirs Inc | USA | 1286 | | Amica Models & Co. | Italy | 1401 | | Anna's Decorations, Ltd | Australia | 1611 | ...

Summary

• Use the IS NULL operator to test if a value is NULL or not. The IS NOT NULL operator negates the result of the IS NULL operator.
• The value IS NULL returns true if the value is NULL or false if the value is not NULL.
• The value IS NOT NULL returns true if the value is not NULL or false if the value is NULL.