MySQL IS NULL Operator

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)