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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
