Just some explicit addition:
> So in the following query:
> select * from new_payments where closed<>1;
> it is desired that null=1. DeMorgan's law takes a vacation here.
You use two-valued logic here, where statements are either "true" or
"false". (DeMorgan's law applies to two-valued logic only.)
When NULL values are not excluded, SQL uses a three-valued logic, where
a stament may also be "unknown". Comparing NULL to any value (including
a comparison of NULL and NULL) always results in "unknown".
This also the reason that the SQL syntax does not allow
... WHERE value = NULL
but requires that you write
... WHERE value IS NULL
It has also been said that NULL is no value but a state - maybe that
helps in understanding.
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com