Re: Query plan for NOT IN

Kevin Grittner wrote:
> Grzegorz Jaœkiewicz<gryzman(at)gmail(dot)com> wrote:

> A failing of the SQL standard is that it uses the same mark (NULL) to
> show the absence of a value because it is unknown as for the case
> where it is known that no value exists (not applicable). Codd argued
> for a distinction there, but it hasn't come to pass, at least in the
> standard. If anyone could suggest a way to support standard syntax
> and semantics and add extensions to support this distinction, it might
> be another advance that would distinguish PostgreSQL from "less
> evolved" products. :-)

Theoretically, the distinction already exists. If you don't know a
person's middle initial, then set it to null; if you know the person
doesn't have one, set it to the empty string.

But from a practical point of view, that wouldn't go very far. Most
*people* equate an empty string to mean the same as null. When I wrote
my own data access layer years ago, I expressly checked for empty
strings on input and changed them to null. I did this because empty
strings had a nasty way of creeping into our databases; writing queries
to produce predictable results got to be very messy.

--
Guy Rouillier