From: Nick Stone <nick@harelane.com>
To: 'Grant Morgan' <grant@ryuuguu.com>
To: pgsql-sql@postgresql.org
Subject: Re: left joins
Date: Wed, 6 Jul 2005 11:33:03 +0100
Message-ID: <20050706103303.3C2C424E7A4@smtp.nildram.co.uk> (raw)
In-Reply-To: <op.sthktgypuogdv0@localhost.localdomain>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nick@harelane.com, grant@ryuuguu.com
Subject: Re: left joins
In-Reply-To: <20050706103303.3C2C424E7A4@smtp.nildram.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox