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.