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.
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.