Re: Full Outer Join with dates as index?

On Tue, 7 Oct 2003, Bruche,ME (pgr) wrote:

> I need to do something like a full outer join on two tables that contain
> dates and prices that were observed on these dates (call them the prices
> of apples and oranges), but I want only one column of dates. On some
> days, the price of apples is observed but the price of oranges is not
> observed, and vice versa. The tables look something like this:

If the columns do have the same names, I think the easiest way is
something like:
select * from price_of_oranges FULL OUTER JOIN price_of_apples USING
(date)
which should mosh the two date columns together in the way you want.

If not, you could probably use something like:
select coalesce(a.date1,b.date2), a.price_of_oranges,b.price_of_apples
from price_of_oranges a full outer join price_of_apples b on
(a.date1=b.date2)