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

i have table looks following:

+---------+----------+-----------------+ | name    |   age    | status          | +---------+----------+-----------------+ | clark   |   25     | example         | +---------+----------+-----------------+ | peter   |   28     | example2        | +---------+----------+-----------------+ | waldo   |   37     | null            | +---------+----------+-----------------+ | tarzan  |   31     | unknown         | +---------+----------+-----------------+ 

when perform query so:

select * records status not 'example%' 

i get:

+---------+----------+-----------------+ | name    |   age    | status          | +---------+----------+-----------------+ | tarzan  |   31     | unknown         | +---------+----------+-----------------+ 

if change query (notice i removed not):

select * records status 'example%' 

i following:

+---------+----------+-----------------+ | name    |   age    | status          | +---------+----------+-----------------+ | clark   |   25     | example         | +---------+----------+-----------------+ | peter   |   28     | example2        | +---------+----------+-----------------+ 

my question is: where's waldo?

in sql, null values cannot participate in comparison operations, including like, instead must consider null values separately using is null operator:

where (`status` not '%example%' or `status` null) 

it's silly, that's life.

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

you cannot use [...] comparison operators such =, <, or <> test null. because result of [...] comparison null null, cannot obtain meaningful results such comparisons.