Re: "=" operator vs. "IS"

> I'm just curious - why is it not possible to use the "=" operator
> to compare values with NULL? I suspect that the SQL standard
> specified it that way, but I can't see any ambiguity in an
> expression like "AND foo.bar = NULL". Is it because NULL does not
> "equal" any value, and the expression should be read as "foo.bar is
> unknown"? Or is there something else I'm missing?

You've got it. NULL is the _absence_ of a known value so any
comparison or operation on it yields an unknown result.

So why can't you use = NULL?

Consider the a list of names and ages where Jack's and Jill's ages are
null. Now we run a query to list people who are of the same age.
Should Jack and Jill be listed as being the same age? Of course not.
You can't compare whether the two unknown values are equal any more
than you could determine whether or not they are over 18.

The SQL spec and PostgreSQL properly use and enforce this
interpretation of NULL.

The correct way to ask your questions is ...where foo.bar is null...

Cheers,
Steve