left joins

From: "Grant Morgan" <grant(at)ryuuguu(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: left joins
Date: 2005-07-06 10:01:54
Message-ID: op.sthktgypuogdv0@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I am having a problem with left joins in Postgresql.(probably my misunderstanding of left joins)

My first Query returns
70,000

select count(*)
from h
where h.tn > 20
and h.tn < 30

my left join
returns only 34,000

select count(*)
from h left join p using (r,pos)
where h.tn > 20
and h.tn < 30
and p.r_order=1

since it is a left join I though I should get a number no smaller in the left join than the original unjoined query. It seems to be acting like an inner join. Both of these are tables not views and both have hash indices on r column. I have tried left joins, right joins , and both using and on , nothing seems make a difference.
Questions
1)should a left join return atleast as many rows as the unjoined left table?
2)am I doing something wrong above?
3)If am not doing anything wrong is this postgresql problem and is there a work around?

Cheers,
Grant

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2005-07-06 10:30:07 Re: left joins
Previous Message Din Adrian 2005-07-05 22:52:21 Re: Help on Procedure running external function