I've had exactly yhe same problem - try changing the query to.
select count(*)from h left join p using (r,pos) and p.r_order=1
where h.tn > 20
and h.tn < 30
I think that should do it - the syntax you used would work in Oracle and MS
SQL but there's a subtle difference with the way Postgres works that means
that any NULLS in the right hand side of the join will be ignored
effectively making it an inner join
Hope this helps
Nick
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Grant Morgan
Sent: 06 July 2005 11:02
To: pgsql-sql@postgresql.org
Subject: [SQL] left joins
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org