Re: behavior of ' = NULL' vs. MySQL vs. Standards

On Wed, 6 Jun 2001, Mark Stosberg wrote:

> 
> Hello,
> 
> I'm a long time Postgres user who uses MySQL when I have to. I recently
> ran into an issue with MySQL where this construct didn't do what I expect:
> 
> WHERE date_column = NULL
> 
> I expected it to work like "date_column IS NULL" like it does it
> Postgres 7.0.2, but instead it returned an empty result set. 
> 
> After conversing with some folks on the MySQL list, it was mentioned that:
> 
>  * "NULL is *NOT* a value. It's an absence of a value, and doing *any*
> comparisons with NULL is invalid (the result must always be NULL, even
> if you say "foo = NULL")." 
> 
>  * Postgres handling is non-standard (even if it's intuitive.) 
> 
> My questions then are: 1.) What IS the standard for handling NULLs? and
> then 2.) If Postgres handling is different than the standard, what's the
> reason? 
> 
> To me, having " = NULL" be the same as " IS NULL" is intuitive and thus
> useful, but I also like appeal of using standards when possible. :) 

Yes, column = NULL should *never* return true according to the spec (it
should always return NULL in fact as stated).  The reason for breaking
with the spec is AFAIK to work with broken microsoft clients that seem to
think that =NULL is a meaningful test and generate queries using that.

In general, =NULL should be avoided in favor of IS NULL by users that are
generating their own queries.