Ever use MySQL to query all records where a field is false and you didn't get the full set of data you
expected? The problem may be caused by a null value in your field.
A common mistake is to assume that !null is true. Take the following example, where John is the only
person emailed:
+--------------------+
| People |
+--------------------+
| Name | Emailed |
+--------------------+
| John | 1 |
| Jane | 0 |
| Bob | null |
+--------------------+
If you want to grab all people not emailed, you might try:
SELECT * FROM Employee WHERE !Emailed;
However, the resulting table will look like this:
+--------------------+
| People |
+--------------------+
| Name | Emailed |
+--------------------+
| Jane | 0 |
+--------------------+
Bob was left out because !Emailed did not evaluate true. This is because the value null really means
unspecified, or unknown. It's neither true nor false, and you can't take the opposite of a value you
don't know, therefore !null is null.
You might be tempted to write your query like this:
SELECT * FROM Employee WHERE !Emailed AND Emailed IS NULL;
This is a bad idea, because now everytime a programmer wants to grab all people not emailed, they have to
remember to include 'AND Emailed IS NULL'.
The proper solution to the problem is to define your database in such a way that a null value cannot
exist, assuming that this is the case, sometimes you may want to have a field be either true, false or
null. However, in this case, we'll say that we always know that a person has or hasn't been emailed.
We implement this solution by disallowing null as an acceptable value in the Emailed field and changing
the default value.
ALTER TABLE People CHANGE Emailed Emailed TINYINT NOT NULL DEFAULT '0';
This will automatically set all null values
to the default value.
Now when we query for all !Emailed people, we will get back the expected results:
SELECT * FROM Employee WHERE !Emailed;
+--------------------+
| People |
+--------------------+
| Name | Emailed |
+--------------------+
| Jane | 0 |
| Bob | 0 |
+--------------------+
http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html
