Re: RFC: A brief guide to nulls

This should be added (From PostgreSQL integrated docs...):

SQL uses a three-valued Boolean logic where the null value represents
"unknown". Observe the following truth tables:

a | b | a AND b | a OR b
------+-------+---------+--------
TRUE | TRUE | TRUE | TRUE
TRUE | FALSE | FALSE | TRUE
TRUE | NULL | NULL | TRUE
FALSE | FALSE | FALSE | FALSE
FALSE | NULL | FALSE | NULL
NULL | NULL | NULL | NULL

So, if there's any trues in the chain of ORs, the whole expression will be
true, not null. This conforms to NULL representing unknown value. If you
have "true or unknown", of course whole result is true regardless of the
"unknown". Let's check this example:

> Subqueries and nulls
> ====================
> Since tests always fail when testing against nulls you can have unexpected
> results with sub-queries.
>
> Example: Assume we have a companies table and a diary table. Diary entries
> are usually related to a particular company but not always.
> SELECT co_name FROM companies WHERE co_id NOT IN (SELECT dy_company FROM
> diary);
> If any row in diary contains a null dy_company then you will get *no
> results*.
> We can expand the query like so:
> WHERE co_id NOT IN (SELECT dy_company FROM diary)
> WHERE co_id NOT IN (1, 2, null, 3...)
> WHERE NOT (co_id=1 OR co_id=2 OR co_id=null OR co_id=3...)
> WHERE NOT (... OR null OR ...)

> WHERE NOT (null)
(erm... actually not)

template1=# select 5 in (1, 2, 9, null);
?column?
----------

(1 row)

template1=# select 2 in (1, 2, 9, null);
?column?
----------
t
(1 row)

WHERE NOT (null/true) -> evaluates to
WHERE null/false

So the result was the same - but only for this example. Suppose you take
the NOT away:

template1=# select * from a where a.i in (1, 2, 9, null);
i
---
1
2
(2 rows)

I surely see two lines (not 0)... :)

And this could be added too, for clarification: "SELECT clause lists lines
for which the WHERE expression is certainly known to be true." ;)

--
Antti Haapala