Mysql test against value excludes NULL records - can anyone explain?

Question

I have a shop_categories table with a category_is_hidden field that is defined as:

category_is_hidden tinyint(4) DEFAULT NULL

In the database, the values ​​for this field are 1 or NULL.

 SELECT * FROM shop_categories where category_is_hidden IS NULL 

returns all null entries.

 SELECT * FROM shop_categories where category_is_hidden <> 1 

returns empty sets (i.e. excludes null values).

Why doesn't the last statement contain null entries? not null <> 1 </p>

Edit: tested on MySQL 5.1 and 5.5

Answer 1

Since your category_is_hidden column is a flag, I would change it to tinyint (1) and make it 1 or 0 instead of 1 or NULL. Setting the column to zero will add bytes to the storage requirements of the column, which will increase the size of the index.

Then, the question that you really asked. NULL by definition UNKNOWN. Your request says: "Give me everything where category_is_hidden is not 1". But the values ​​of the NULL columns are unknown. Thus, MySQL does not know if they are 1. You need to rewrite WHERE as NOT. If your column will be three-digit (1, NULL, another value), you need to make sure you have OR to allow this.

Answer 2

If the field is NULL, then it does not matter. This is not a zero or an empty string. If you check if NULL <> 1, then this is not so, because it is not a number; it is not something and therefore cannot be compared.