MySQL is not returning NULL values.

A couple of days ago I was working on a rather simple filtering function in a system that I’m building. I added a dropdown box in the UI where a user can select a status to exclude from a table view with results from a MySQL database.

A simplified version of the MySQL table looks something like this:

CREATE TABLE `orders` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `status` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

In the status column, the table had string data but also NULL values since that column schema allowed NULL for that column. Here’s an example of what kind of data that was in the table.

INSERT INTO `orders` (`id`, `status`)
VALUES
  (1, 'processed'),
  (2, 'pending'),
  (3, NULL),
  (4, 'shipped'),
  (5, ''),
  (6, 'paid');

The frontend part of the system sends requests to an API backend which then executes a MySQL query that looked something like this:

SELECT * FROM orders WHERE status <> 'pending'

I was expecting to get all the results back except the record with ID = 2 since that row has the value of the status that I was excluding. However, to my surprise I got this:

id  status
1 processed
4 shipped
5 ''
6 paid

I found out later, that when you use not equal to (<> or !=) in MySQL it also excludes all NULL values. To include NULL values you have to explicitly include them by running a query like so:

SELECT * FROM orders WHERE status != 'pending' OR status IS NULL

Some of you out there might know this already, but it was the first time for me to stumble upon this, so I thought I’d write a quick blog post to share what I discovered and learned.

Hope it was useful!

Until next time, have a good one!