Today I learnt that MySQL's 'not equal' operator (
<>) 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
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
1, you might think you could just use the condition
template_id != 1.
SELECT * FROM pages 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:
SELECT * FROM pages 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 (
SELECT * FROM pages 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.