This SQL tutorial focuses on the MySQL IS NULL operator, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.
This tutorial is a part of several posts explaining how to use the WHERE clause in MySQL. To read additional posts regarding this subject, please use the following links:
- Introduction – MySQL WHERE clause
- Basic comparison operators
- IN operator
- BETWEEN operator
- LIKE operator
- IS NULL operator
- AND & OR operators
MySQL IS NULL / IS NOT NULL
In MySQL, NULL value indicates an unavailable or unassigned value. The value NULL does not equal zero (0), nor does it equal a space (‘ ‘). Because the NULL value cannot be equal or unequal to any value, you cannot perform any comparison on NULL values by using operators such as ‘=’ or ‘<>’.
The following MySQL queries will produce no results:
The books without any discount:
SELECT bookID, bookName, bookPrice, discount FROM books WHERE discount = NULL -- (no rows will be retrieved)
books with a discount:
SELECT bookID, bookName, bookPrice, discount FROM books WHERE discount <> NULL -- (no rows will be retrieved)
In MySQL, to handle comparison with NULL values, you need to use the following MySQL operators:
- IS NULL – equals the logical operation ‘= NULL’ (records with NULL values)
- IS NOT NULL – equals the logical operation ‘<> NULL’ (records without NULL values)
The books without a discount:
SELECT bookID, bookName, bookPrice, discount FROM books WHERE discount IS NULL -- (error)
The books with a discount:
SELECT bookID, bookName, bookPrice, discount FROM books WHERE discount IS NOT NULL -- (error)