Re: joining tables

oayasli(at)gmail(dot)com writes:

> Hi,
>
> If you have two tables, each with a column called "keys" and a column
> called "values", and they are both incomplete, such as:
>
> table 1:
>
> keys | values
> -----+----------
> 1 | (null)
> 2 | two
> 3 | (null)
>
> table 2:
>
> keys | values
> -----+---------
> 1 | one
> 2 | (null)
> 3 | three
>
> is there a way to join them, in order to get:
>
> keys | values
> -----+---------
> 1 | one
> 2 | two
> 3 | three
>
> The closest I could get was with NATURAL FULL JOIN:
>
> keys | values
> -----+---------
> 1 | one
> 1 | (null)
> 2 | two
> 2 | (null)
> 3 | three
> 3 | (null)
>
> Thanks

Try something like:

SELECT key,
CASE when table1.value IS NOT NULL THEN k1.value
ELSE table2.value END as value
FROM table1
FULL JOIN table2 USING(key);

You might want to use 'IS DISTINCT FROM table2.value' if you want the
value for table1 to be returned in preference to table2.value.