> I believe the accepted spelling of that query is
> SELECT count(*) FROM t1 WHERE b IS NULL;
> (or IS NOT NULL). I don't know either what the SQL standard has to
> say about the issue --- does it expect "= NULL" to be a synonym for
> "IS NULL"?
afaik SQL92 does not define/allow "= NULL". However, our friends at M$
use this syntax in queries generated by M$Access, presumably pulling
their usual BS in altering standards to reduce interoperability.
Someone very recently brought this up, and the "= NULL" will be
synonymous with "IS NULL" in the next release (and patches are likely to
be available beforehand).
> The CREATE FUNCTION example does seem to illustrate that it'd be nice
> if "=" and "!=" worked on NULL values. I'd still object to trying
> to define an order that includes NULL, so "3 < NULL" should return
> NULL, but I can see the reasonableness of defining "3 != NULL" as
> TRUE.
Sorry, got to go with D'Arcy on this one. C.J. Date in his recent book
"A Guide to the SQL Standard" points out the inconsistancies within
SQL92 regarding tri-value booleans and nulls. However, it is the case
that one can mostly assume that any comparison involving a NULL will
return false. Null usually means "don't know", not "isn't", but
expressions are unfortunately required to resolve to true or false.
- Tom