Re: Can't use NULL in IN conditional?

On Monday 11 December 2000 12:34, Tom Lane wrote:
> 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

Thanks for the clarification and SQL reference. I spent some time on this
today and updated a file of mine at

http://www.comptechnews.com/~reaster/dbdesign.html#three-valued-logic

to take into consideration these things. If you do take a look at it and
find an error, I will fix it. This dbdesign.html file is a file linked to
from http://postgresql.readysetnet.com/docs/faq-english.html so I'm hoping to
keep it correct and useful. Thanks :)

--
-------- Robert B. Easter reaster(at)comptechnews(dot)com ---------
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------