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)
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)
MySQL IS NULL – specialized features
To be compatible with ODBC programs, MySQL supports some specialized features of the IS NULL operator.
1) Treatment of date ‘0000-00-00’
1) If a DATE or DATETIME column has a NOT NULL constraint and contains a special date '0000-00-00', you can use the IS NULL operator to find such rows.
First, create a table called projects:
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)
);Code language: SQL (Structured Query Language) (sql)
Second, insert some rows into the projects table:
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');Code language: SQL (Structured Query Language) (sql)
Third, use the IS NULL operator to select rows with the values in the complete_date column is '0000-00-00'.
SELECT *
FROM projects
WHERE complete_date IS NULL;Code language: SQL (Structured Query Language) (sql)
2) Influence of @@sql_auto_is_null variable
If the variable @@sql_auto_is_null is set to 1, you can get the value of an auto_increment column after executing an INSERT statement by using the IS NULL operator.
Note that by default the variable @@sql_auto_is_null is 0. Consider the following example.
First, set the variable @@sql_auto_is_null to 1.
SET @@sql_auto_is_null = 1;Code language: SQL (Structured Query Language) (sql)
Second, insert a new row into the projects table:
INSERT INTO projects(title,begin_date, complete_date)
VALUES('MRP III','2010-01-01','2020-12-31');Code language: SQL (Structured Query Language) (sql)
Third, use the IS NULL operator to get the generated value of the id column:
SELECT
id
FROM
projects
WHERE
id IS NULL;Code language: SQL (Structured Query Language) (sql)
Summary
- Use the
IS NULLoperator to test if a value isNULLor not. TheIS NULLoperator returns one if a value isNULL. - The
IS NOT NULLreturns one if a value is notNULL.