Thread: 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 seemsto be acting like an inner join. Both of these are tables not views and both have hash indices on r column. I have triedleft 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
Grant Morgan wrote: > 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. You don't. You make the left join, then apply another condition "p.r_order=1". If you want to demonstrate a problem, you'll need to make sure the queries are the same. -- Richard Huxton Archonet Ltd
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
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 > >
"Grant Morgan" <grant@ryuuguu.com> writes:
> 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.
Well, yeah. The condition p.r_order=1 will return NULL (effectively
FALSE) for any row in which p.r_order is NULL, so none of the
null-extended rows can survive the WHERE filter, so it's effectively
an inner join. Recent versions of PG actively recognize this case
and reduce the LEFT JOIN to plain JOIN, but even if we did not do that
you'd get the same result.
I've heard it claimed that Oracle produces different results; if true,
it must have something to do with their rather standards-challenged
interpretation of NULL ...
regards, tom lane
On Wed, 2005-07-06 at 11:33 +0100, Nick Stone 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 really ? is this legal SQL ? is this a 8.0 feature ? I get syntax error at or near "and" at character 41 gnari
On 7/6/05, Ragnar Hafstað <gnari@simnet.is> wrote: > On Wed, 2005-07-06 at 11:33 +0100, Nick Stone 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 > > really ? is this legal SQL ? > is this a 8.0 feature ? > I get syntax error at or near "and" at character 41 > SQL like this works for me when I write it with an ON statement instead of a USING. select count(*)from h left join p on (h.r=p.r and h.pos=r.pos and p.r_order=1)where h.tn > 20and h.tn < 30 Filtering within the join condition is very useful when doing a left outer join. Here's another example "from the book" doing this type of filter within the join: http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.html Tony Wasson