Why doesn't this left outer join query work properly?

Question

I have a table that looks like this:

peter=> \d aggregated_accounts_by_month
Table "public.aggregated_accounts_by_month"
  Column   |  Type   | Modifiers 
-----------+---------+-----------
 xtn_month | date    | 
 account   | text    | 
 commodity | text    | 
 amount    | numeric | 
Indexes:
    "idx_aggregated_accounts_by_month_account" btree (account)
    "idx_aggregated_accounts_by_month_month" btree (xtn_month)

And another table that looks like this:

peter=> \d months
   Table "pg_temp_2.months"
  Column   | Type | Modifiers 
-----------+------+-----------
 xtn_month | date | 

months contains this:

 xtn_month   
------------
 2011-01-01 
 2011-02-01 
 2011-03-01 
 2011-04-01 
 2011-05-01 
 2011-06-01 
 2011-07-01 

aggregated_accounts_by_month contains this relevant data:

 xtn_month  |    account    | amount 
------------+---------------+--------
 2011-01-01 | Expenses:Fuel | 111.31
 2011-02-01 | Expenses:Fuel |  89.29
 2011-03-01 | Expenses:Fuel |  97.41
 2011-04-01 | Expenses:Fuel | 101.70
 2011-05-01 | Expenses:Fuel |   52.9
 2011-07-01 | Expenses:Fuel |  49.55

The query I'm trying to run is:

select 
    months.xtn_month,
    account,
    amount
from
    aggregated_accounts_by_month a
    left outer join months on months.xtn_month = a.xtn_month
where
    account = 'Expenses:Fuel'
order by
    xtn_month;

What I want this query to do is give me these results:

 xtn_month  |    account    | amount 
------------+---------------+--------
 2011-01-01 | Expenses:Fuel | 111.31
 2011-02-01 | Expenses:Fuel |  89.29
 2011-03-01 | Expenses:Fuel |  97.41
 2011-04-01 | Expenses:Fuel | 101.70
 2011-05-01 | Expenses:Fuel |   52.9
 2011-06-01 | Expenses:Fuel |
 2011-07-01 | Expenses:Fuel |  49.55

But it's actually giving me this:

 xtn_month  |    account    | amount 
------------+---------------+--------
 2011-01-01 | Expenses:Fuel | 111.31
 2011-02-01 | Expenses:Fuel |  89.29
 2011-03-01 | Expenses:Fuel |  97.41
 2011-04-01 | Expenses:Fuel | 101.70
 2011-05-01 | Expenses:Fuel |   52.9
 2011-07-01 | Expenses:Fuel |  49.55

I'm clearly doing something wrong. Any ideas? I'm running PostgreSQL 9.0.4 on Mac OS X 10.6.7.

Edit: After thinking about this some more, I need to left outer join against not only months, but also against accounts. This query does exactly what I want:

select
    xtn_month,
    account,
    coalesce(amount, 0)
from
    (
        select
            xtn_month,
            account
        from
        (
            select
                distinct xtn_month
            from
                aggregated_accounts_by_month
        ) x
        cross join
        (
            select
                distinct account
            from
                aggregated_accounts_by_month
        ) y
    ) z
    left outer join aggregated_accounts_by_month
        using (xtn_month, account)
where
    account = 'Expenses:Fuel'
order by
    xtn_month;

ypercube's answer was almost right, except that it didn't fill in the account column. This query is of course rather expensive, what with that cross product in there. That's ok, though, because aggregated_accounts_by_month has a little under 2000 rows for over four years of data.

Answer 1

Two things:

• reverse the order of the two tables in the LEFT JOIN and
• move the condition from the WHERE to the ON clause.

.

select 
    months.xtn_month,
    a.account,
    a.amount
from
    months 
    left outer join aggregated_accounts_by_month a
        on  months.xtn_month = a.xtn_month
        and a.account = 'Expenses:Fuel'
order by
    xtn_month;

Answer 2

Your aggregated_accounts_by_month contains no 2011-06-01. You might actually be looking for a full join:

select 
    months.xtn_month,
    account,
    amount
from
    aggregated_accounts_by_month a
    full join months on months.xtn_month = a.xtn_month
where
    account = 'Expenses:Fuel'
order by
    xtn_month;

Alternatively, left join as the other two answers suggest, i.e. months, then aggregated_accounts_by_month.