Re: "IS NOT NULL" != "NOT NULL"

Sean Chittenden <sean(at)chittenden(dot)org> writes:
> In my mind: "col2 != NULL" is the same as "col2 IS NOT
> NULL", but I fully understand why "col2 = NULL" is an invalid
> statement.

To me, "col2 != NULL" means "NOT (col2 = NULL)". Does that help it
make more sense to you?

The reason SQL has the special IS NULL and IS NOT NULL constructs
is exactly that you can't do anything useful with "foo = NULL" or
"foo != NULL".

If you want to get into language-lawyering: I believe that in pure
SQL92 you can't even validly write an unadorned NULL as a constant
in an expression; you're supposed to cast it to some type, viz
"CAST(NULL AS something)". Postgres is lax about this since we have
ambiguous-type resolution machinery in the parser anyway. Perhaps
the reason why certain other DBMSes thought they could get away with
interpreting "foo = NULL" as "foo IS NULL" is that "foo = NULL" is
illegal according to the strict text of the standard, and thus arguably
doesn't have a standardized meaning; even though anyone who's grasped
the SQL rules for NULL would expect it to yield NULL.

regards, tom lane