I'm sure there's a perfectly reasonable explanation for all of this, but if there is I have no idea what it is.
If you're using Mysql, you're better not not using NULL values at all. Save yourself the confusion.
They often point out that newcomers can be confused because an empty string is not the same as NULL, so you can set a NULLable field to equal "". To test for NULL you should use IS NULL or IS NOT NULL. Also, math doesn't work with NULL. 1+NULL = NULL. Okay, once you understand what NULL is, a non-value, that makes sense.
What what they don't tell you is that NULL is a sneaky bitch. If you test for ANY condition on that field, it won't return rows with NULL.
Some examples to show what I'm talking about:
SELECT * FROM table;
You get all rows, of course.
SELECT * FROM table WHERE field IS NULL;
You get all rows where the field is set to NULL, naturally.
SELECT * FROM table WHERE field = 1;
Obviously you get the rows where field is equal to 1.
SELECT * FROM table WHERE field != 1;
Of course you get all rows where field does not equal 1... or do you? No! You don't. You get all rows where field does not equal 1 AND where the field IS NOT NULL.
And it doesn't matter how you check values.
SELECT * FROM table WHERE field NOT IN (1, 3, 7);
Lovers of logic sob into their pillows at night, while NULL gets lost in dark and forbidden streets of your data, probably meeting undesirables and becoming addicted to drugs, perhaps even prostituting for its next fix. It's true. No field value should be forced into a situation like that.
If you want to return results including NULLs, you have to add it to the query:
SELECT * FROM table WHERE field <> 1 OR field IS NULL;
This is true of MySQL 5.0 and 5.1, I don't know if other databases are the same way.
Perhaps this logic is meant to be implied when the MySQL documentation states that "In SQL, the NULL value is never true in comparison to any other value, even NULL." 1 = NULL is false, 1 != NULL is false, and even NULL = NULL is false. Personally I think such a significant affect on query results should have been noted much more clearly than that. They note that you have to check for NULL using different syntax, but they don't say that if you're checking for anything else you also have to give NULL special treatment if you want to avoid needing Rogaine later.
The moral of this story? Set your fields to not allow NULL, you'll be annoyed that you were forced to treat a non-value with some placeholder like 0, but at least you'll never wonder why your database is acting like a smart ass.
