Mysql can't select the record in the table

Question

I have a mysql table with a null-able int column named 'status', and I have two records in the table where one of the status is 2 when the other is NULL, but when I select the records with query 'status!=2', the record (status=null) is not shown.

mysql> 
mysql> 
mysql> desc admin_user;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| acct_name   | varchar(32)      | YES  |     | NULL    |                |
| password    | varchar(32)      | YES  |     | NULL    |                |
| user_name   | varchar(32)      | YES  |     | NULL    |                |
| description | varchar(128)     | YES  |     | NULL    |                |
| status      | int(11)          | YES  |     | NULL    |                |
| role        | int(11)          | NO   |     | 1       |                |
| create_date | date             | YES  |     | NULL    |                |
| update_date | date             | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> select id, acct_name, status from admin_user;
+----+-----------+--------+
| id | acct_name | status |
+----+-----------+--------+
|  1 | letme     |   NULL |
|  3 | admin     |      2 |
+----+-----------+--------+
2 rows in set (0.00 sec)

mysql> select id, acct_name, status from admin_user where status=2;
+----+-----------+--------+
| id | acct_name | status |
+----+-----------+--------+
|  3 | admin     |      2 |
+----+-----------+--------+
1 row in set (0.00 sec)

mysql> select id, acct_name, status from admin_user where status IS NULL;
+----+-----------+--------+
| id | acct_name | status |
+----+-----------+--------+
|  1 | letme     |   NULL |
+----+-----------+--------+
1 row in set (0.00 sec)

mysql> select id, acct_name, status from admin_user where status!=2;
Empty set (0.00 sec)

mysql>

As you can see, the record whose status is NULL can not be selected using the query 'status!=2'. I also tried 'status<>2'. Can anybody help?

Answer 1

Nulls are really interesting.

The NULL value can be surprising until you get used to it. Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.

What's more

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL

There are lots of different ways to write this. One of them being:

select id, acct_name, status from admin_user where status IS NULL
  OR status != 2

Alterantively as @shA.t suggested.

select id, acct_name, status from table1 where COALESCE(status, 0)  != 2

Just check that 0 is indeed a number that doesn't appear any where else in the table.