Re: Can't use NULL in IN conditional?

"Robert B. Easter" <reaster(at)comptechnews(dot)com> writes:
> What you are saying agrees with things I've read elsewhere, and a little
> definition/note that I wrote on my "Databasing" Terms page:

> three-valued logic:
> a logic system that employs TRUE, FALSE, and UNKNOWN. NULL values introduce
> UNKNOWN into boolean operations. A truth table must be used to lookup the
> proper value (TRUE or FALSE) of UNKNOWN under specific operations. In SQL
> implementations that use three-valued logic, you must consult the
> documentation for its truth table.

Three-valued logic is perfectly straightforward if you keep in mind the
interpretation of NULL/UNKNOWN: "I don't know if this is true or false".
Thus:

NOT unknown => unknown

false AND unknown => false (it can't possibly be true)
true AND unknown => unknown
unknown AND unknown => unknown

false OR unknown => unknown
true OR unknown => true (it's true no matter what the unknown is)
unknown OR unknown => unknown

For ordinary operators such as "=", the result is generally NULL if any
input is NULL, although there are some specific cases where you can
deduce a correct result knowing only some of the inputs. In particular,
NULL = NULL does not yield TRUE, it yields UNKNOWN. This is correct
when you consider that NULL is not a specific value, but a placeholder
for an unknown value. (Reference: SQL99 section 8.2 general rule 1a.)

IS NULL and IS NOT NULL are not ordinary operators in this sense, since
they can deliver a non-null result for NULL input.

Also, SQL specifies that a WHERE clause that evaluates to "unknown" is
taken as false, ie, the row is not selected.

Bottom line is that in a spec-conformant implementation,
WHERE code = '0A' OR code = NULL
will act the same as if you'd just written "WHERE code = '0A'"; the
second clause always yields unknown and so can never cause the WHERE to
be taken as true.

> Some newer implementations of SQL
> eliminate UNKNOWN, and may generally behave as follows: all boolean tests
> involving NULL return FALSE except the explicit test IS NULL, e.g., if NULL
> is a possibility, it has to be tested for explicity using IS NULL or IS NOT
> NULL.

They may *appear* to return FALSE if you aren't looking too closely,
since WHERE treats top-level results of FALSE and UNKNOWN the same.
If they really don't make the distinction then they are broken.
AFAICT, neither SQL92 nor SQL99 regard NULL support as optional.

regards, tom lane