Re: Select .... where id not in (....) returns 0 incorrectly

On 2022-04-05 19:25:24 -0400, Mladen Gogala wrote:
> NULL is strange. Relational databases use ternary, not binary logic.
> In the woke vernacular, one could say that Postgres is non-binary.
> NULL literally means "no value".

I prefer to think of NULL as "unknown value". That way the ternary logic
makes intuitive sense:

NULL = NULL? If you have two unknown values you don't know whether they
are the same or not, so the result is also unknown, i.e. NULL.

> It is a part of the standard, so we have to deal with it,
> Codd help us.

:-)

> However, based on my lifelong experience with Oracle, NULL values are
> bad and are best avoided.

Oracle's handling of NULL values has a few extra warts, yes. I still
wouldn't go as far as recommending to avoid NULL values (where they make
sense semantically).

> Postgres is more forgiving than Oracle because in Postgres, the
> condition "is not null" can be resolved by index. In Oracle, it can
> not.

Actually it can (although it's a full index index scan, so the optimizer
may prefer not to). It's "is null" which cannot use an index, because
btree indexes in Oracle don't store NULL values (bitmap indexes do store
NULL values, though - are they still an enterprise feature?).

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"