Mysql test against value excludes NULL entries - can someone explain?

Question

I've got a table shop_categories with a field called category_is_hidden which is defined as:

category_is_hidden tinyint(4) DEFAULT NULL

In the database, the values for that field are either 1 or NULL.

SELECT * FROM shop_categories where category_is_hidden IS NULL

returns all the null entries.

SELECT * FROM shop_categories where category_is_hidden <> 1

returns an empty sets (that is, it excludes the null values).

Why does that last statement not include null entries? isn't null <> 1?

Edit: Tested on MySQL 5.1 & 5.5

Answer 1

Since your category_is_hidden column appears to be a flag, I'd change it to tinyint(1) and make it be either 1 or 0 instead of 1 or NULL. Allowing a column to be null will add a byte to the storage requirements of the column, leading to an increased index size.

Next, the question you actually asked. NULL by definition is UNKNOWN. Your query says "give me everything where category_is_hidden is not 1". But the NULL column values are all unknown. So MySQL doesn't know if they are not 1. You need to rewrite the WHERE as IS NOT NULL. If your column is going to be tri-state (1, NULL, other value), you need to make your WHERE have an OR in it to allow for that.

Answer 2

If a field is null, then it means that it does not have a value. It is not zero, or an empty string. If you check if NULL <> 1, then it is not, because it is not a number; it is not anything, and therefore cannot be compared.