Re: joining tables

oayasli@gmail.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.