Skip to content
MySQL Today I Learned

MySQL's not equal operator doesn't match null values

2 min read

Today I learnt that MySQL's 'not equal' operator (!= or <>) does not match null values unless you are specifically doing a comparison on a null value.

For example, let's imagine we've got a pages table with a template_id column:

id | name    | template_id
1  | Home    | 1
2  | About   | null
3  | Support | null
4  | FAQs    | 2

If we want to get all pages except those with the template_id of 1, you might think you could just use the condition template_id != 1.

    WHERE template_id != 1;

However, for our example data, this would only return the 'FAQs' page. The 'About' and 'Support' pages have null values for the template_id which means MySQL doesn't know if they could be 1 or not.

To get the correct result, we need to include a condition for the null values:

    WHERE template_id != 1 OR template_id IS NULL;

This will give us all pages except for the 'Home' page.

There's another way of writing this, which is even simpler. We can use the null-safe equals operator (<=>):

    WHERE NOT template_id <=> 1;

Despite having worked with MySQL for many years, this is the first time I've come across this particular situation. It's good to know that there was a simple solution.

© 2024 Andy Carter