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.