[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]
Re: [SQL] left joins
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] left joins
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 <[EMAIL PROTECTED]> 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: [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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] left joins
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
Re: [SQL] left joins
"Grant Morgan" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Prepare plan in plpgsql
Hi, i'm wondering if there is a way to prepare and execute a plan in a plpgsql function. The prepare seems to work as expected but obviously the execute plan_name(...) statement is interpreted as the plpgsql execute keyword and says me that the plan_name function don't exists is there a way to use prepared queries in plpgsql? if not what is the best way to pass a parameter directly to a query? (im using postGIS and it would be nice if I could use another way that unparsing and parsing a geometry parameter in an execute dynamic query) thank you and have all a nice day! ---(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
Re: [SQL] Prepare plan in plpgsql
Jocelyn Turcotte <[EMAIL PROTECTED]> writes: > i'm wondering if there is a way to prepare and execute a plan in a > plpgsql function. You do not need that because plpgsql automatically caches plans for SQL statements appearing in a plpgsql function. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] left joins
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] left joins
On 7/6/05, Ragnar Hafstað <[EMAIL PROTECTED]> 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 > 20 and 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
