To: pgsql-sql@postgresql.org
Subject: Re: left joins
References: <20050706103303.3C2C424E7A4@smtp.nildram.co.uk>
Message-ID: <op.sthm49peuogdv0@localhost.localdomain>
Date: Wed, 06 Jul 2005 19:52:11 +0900
From: "Grant Morgan" <grant@ryuuguu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <20050706103303.3C2C424E7A4@smtp.nildram.co.uk>
User-Agent: Opera M2/8.01 (Linux, build 1204)

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
>
>