Thank you Richard and Nick, your right.
And what Nick showed below is what I wanted.
Cheers,
Grant
On Wed, 06 Jul 2005 19:33:03 +0900, Nick Stone <nick@harelane.com> wrote:
> 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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>