Search code examples
mysqlnullsql-like

MySQL: WHERE status NOT LIKE 'example%' isn't returning results with NULL status

I have a table that looks like the following:

+---------+----------+-----------------+
| name    |   age    | status          |
+---------+----------+-----------------+
| Clark   |   25     | Example         |
+---------+----------+-----------------+
| Peter   |   28     | Example2        |
+---------+----------+-----------------+
| Waldo   |   37     | NULL            |
+---------+----------+-----------------+
| Tarzan  |   31     | Unknown         |
+---------+----------+-----------------+

When I perform a query like so:

SELECT * FROM records WHERE status NOT LIKE 'example%'

I get:

+---------+----------+-----------------+
| name    |   age    | status          |
+---------+----------+-----------------+
| Tarzan  |   31     | Unknown         |
+---------+----------+-----------------+

If I change the query to (notice that I removed the NOT):

SELECT * FROM records WHERE status LIKE 'example%'

I then get the following:

+---------+----------+-----------------+
| name    |   age    | status          |
+---------+----------+-----------------+
| Clark   |   25     | Example         |
+---------+----------+-----------------+
| Peter   |   28     | Example2        |
+---------+----------+-----------------+

My question is: where's Waldo?

Solution

  • In SQL, NULL values cannot participate in most comparison operations, including LIKE, instead you must consider NULL values separately using the IS NULL operator:

    WHERE (`status` NOT LIKE '%example%' OR `status` IS NULL)

    It's silly, but that's life.

    This is documented here: https://dev.mysql.com/doc/refman/5.7/en/working-with-null.html

    You cannot use [...] comparison operators such as =, <, or <> to test for NULL. Because the result of any [...] comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons.