MySQL IS NULL Operator

This SQL tutorial focuses on the MySQL IS NULL operatorand 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:


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)

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…