Re: [SQL] Outer joins
(damn gmail, sorry about priv mail earlier) Stephan Szabo <[EMAIL PROTECTED]>: I think putting a test in an ON clause associated with the join (using something like ... left join object_values ov1 ON(ov.obj_id = ov1.obj_id and ov1.att_id=8) rather than where will consider both as part of the join and null extend even if there are obj_id matches if none of those have att_id=8. Thanks, that worked! 2006/4/28, Tom Lane <[EMAIL PROTECTED]>: Emils <[EMAIL PROTECTED]> writes: > The table structure is: > object_values > == This isn't an outer-join problem, it's a crosstab problem. Try the crosstab functions in contrib/tablefunc. Thanks, I looked into it, unfortunately I might have to have also a solution for where installing contrib stuff is not possible. Emils ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Outer joins?
Emils <[EMAIL PROTECTED]> writes: > The table structure is: > object_values > == > obj_id > att_id > value > namely, each object can have arbitrary number of attributes each of > them with a value. > What I want, is a simple table of objects with some of their specific > attributes, the result should be in form: > obj_id1 o1att1_value o1att2_value o1att3_value > obj_id2 o2att1_value o2att2_value o2att3_value > ... This isn't an outer-join problem, it's a crosstab problem. Try the crosstab functions in contrib/tablefunc. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Outer joins?
On Fri, 28 Apr 2006, Emils wrote: > I am trying to do simple self-joins. > > The table structure is: > > object_values > == > obj_id > att_id > value > > namely, each object can have arbitrary number of attributes each of > them with a value. > > What I want, is a simple table of objects with some of their specific > attributes, the result should be in form: > > obj_id1 o1att1_value o1att2_value o1att3_value > obj_id2 o2att1_value o2att2_value o2att3_value > ... > > Obviously, if eg obj2 doesn't have att2 in the table, I want a NULL in > that grid point. > > So, I thought some nested outer joins should be OK? > > SELECT > OV.obj_id AS obj_id, > OV.value AS NAME, > ov1.value AS DESCRIPTION, > ov2.value AS ICON > FROM > object_values OV LEFT JOIN object_values ov1 USING(obj_id) > LEFT JOIN object_values ov2 USING(obj_id) > WHERE OV.att_id=7 AND ov1.att_id=8 AND ov2.att_id=16; AFAIK, effectively first the join happens then the where filter. So, imagine the output of the joins without any where clause and then apply the where clause as a filter upon that. Even if you got NULL extended rows, you'd filter them out because the ov1.att_id and ov2.att_id tests would filter them out. In addition, you won't actually get NULL extended rows I think, because there will always be at least one row with matching obj_id (the one from ov that's being worked on). I think putting a test in an ON clause associated with the join (using something like ... left join object_values ov1 ON(ov.obj_id = ov1.obj_id and ov1.att_id=8) rather than where will consider both as part of the join and null extend even if there are obj_id matches if none of those have att_id=8. Another way of doing the same thing is using subselects in from to filter the right hand tables you wish to join. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Outer joins?
Hello! I am a longtime postgres user (started around 10 years ago), however, as for some years I've been using it mostly as administrator. Now that I have started a project and doing some SQL, I've come up something I don't believe is right. Maybe I am too rusty on my SQL - if so, please forgive me, but I checked it and my reasoning seemed ok to me. I am trying to do simple self-joins. The table structure is: object_values == obj_id att_id value namely, each object can have arbitrary number of attributes each of them with a value. What I want, is a simple table of objects with some of their specific attributes, the result should be in form: obj_id1 o1att1_value o1att2_value o1att3_value obj_id2 o2att1_value o2att2_value o2att3_value ... Obviously, if eg obj2 doesn't have att2 in the table, I want a NULL in that grid point. So, I thought some nested outer joins should be OK? SELECT OV.obj_id AS obj_id, OV.value AS NAME, ov1.value AS DESCRIPTION, ov2.value AS ICON FROM object_values OV LEFT JOIN object_values ov1 USING(obj_id) LEFT JOIN object_values ov2 USING(obj_id) WHERE OV.att_id=7 AND ov1.att_id=8 AND ov2.att_id=16; So, I figured this should get me all objects that have atttribute 7 defined, regardless of whether the other attributes exist for them? However, for some reason PG8.1 is giving me something like an INNER join on this query - namely ONLY rows where ALL the attributes ARE present. Am I doing something wrong? As I said my SQL is rusty, but this looked pretty straightforward to me... Thanks in advance, Emils ---(end of broadcast)--- TIP 1: 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: [HACKERS] [SQL] outer joins strangeness
Alex Pilosov <[EMAIL PROTECTED]> writes: > I'm going to CC this to -hackers, maybe someone will shed a light on the > internals of this. It's not unintentional. See http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] outer joins strangeness
On Mon, 24 Sep 2001, Alex Pilosov wrote: > On Sun, 23 Sep 2001, Stephan Szabo wrote: > > > On Sun, 23 Sep 2001, Alex Pilosov wrote: > > > > Postgres treats join syntax as an explicit definition of what order to > > joins in. So, I'd guess it sees the first as: do the LOJ and then join > > that to the separate table. > Yeah, I figure that's how it sees it, but that's pretty stupid from > performance reasons :P) > > It _should_ realize that left outer join only constricts join order > between two tables in outer join, and joins to all other tables should > still be treated normally. (see below) > > I'm going to CC this to -hackers, maybe someone will shed a light on the > internals of this. > > > And for right outer join (for example), those two queries would not > > be equivalent if I read the ordering correctly. The former syntax > > would mean outer first and then the inner, whereas the second would > > be inner first then the outer, and that could have different results. > True. But this is not right outer join, its a left outer join...:) > > Postgres should understand that left outer join does not constrict join > order... But it can. If your condition was a joining between the other table and the right side of the left outer join, you'd have the same condition as a right outer join and the left side. The real condition I think is that you can join a non-explicitly joined table to the side of an outer join before the outer join but not to the other side. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] outer joins strangeness
On Sun, 23 Sep 2001, Stephan Szabo wrote: > On Sun, 23 Sep 2001, Alex Pilosov wrote: > > > It may be just me, or I am grossly misunderstanding syntax of outer joins, > > but I see that plans for my queries are different depending on how I place > > join conditions and sometimes even on order of the tables. > > > > Example: > > 1: > > explain select * from customers c,orders o left outer join adsl_orders ao > > on ao.order_id=o.order_id > > where c.cust_id=o.cust_id > > and c.cust_id=152 > > > > explain select * from customers c join orders o on c.cust_id=o.cust_id > > left outer join adsl_orders ao on ao.order_id=o.order_id > > where c.cust_id=152 > Postgres treats join syntax as an explicit definition of what order to > joins in. So, I'd guess it sees the first as: do the LOJ and then join > that to the separate table. Yeah, I figure that's how it sees it, but that's pretty stupid from performance reasons :P) It _should_ realize that left outer join only constricts join order between two tables in outer join, and joins to all other tables should still be treated normally. I'm going to CC this to -hackers, maybe someone will shed a light on the internals of this. > And for right outer join (for example), those two queries would not > be equivalent if I read the ordering correctly. The former syntax > would mean outer first and then the inner, whereas the second would > be inner first then the outer, and that could have different results. True. But this is not right outer join, its a left outer join...:) Postgres should understand that left outer join does not constrict join order... -alex ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] outer joins strangeness
On Sun, 23 Sep 2001, Alex Pilosov wrote: > It may be just me, or I am grossly misunderstanding syntax of outer joins, > but I see that plans for my queries are different depending on how I place > join conditions and sometimes even on order of the tables. > > Example: > 1: > explain select * from customers c,orders o left outer join adsl_orders ao > on ao.order_id=o.order_id > where c.cust_id=o.cust_id > and c.cust_id=152 > > > Nested Loop (cost=94.23..577.47 rows=2 width=290) > -> Index Scan using customers_pkey on customers c (cost=0.00..2.02 > rows=1 width=125) > -> Materialize (cost=501.65..501.65 rows=5904 width=165) > -> Hash Join (cost=94.23..501.65 rows=5904 width=165) > -> Seq Scan on orders o (cost=0.00..131.04 rows=5904 > width=58) > -> Hash (cost=86.18..86.18 rows=3218 width=107) > -> Seq Scan on adsl_orders ao (cost=0.00..86.18 > rows=3218 width=107) > > Query 2: > > explain select * from customers c join orders o on c.cust_id=o.cust_id > left outer join adsl_orders ao on ao.order_id=o.order_id > where c.cust_id=152 > > Nested Loop (cost=0.00..9.30 rows=2 width=290) > -> Nested Loop (cost=0.00..5.06 rows=2 width=183) > -> Index Scan using customers_pkey on customers c > (cost=0.00..2.02 rows=1 width=125) > -> Index Scan using orders_idx1 on orders o (cost=0.00..3.03 > rows=1 width=58) > -> Index Scan using adsl_orders_pkey on adsl_orders ao > (cost=0.00..2.02 rows=1 width=107) > > To me, both queries seem exactly identical in meaning, and should generate > the same plans. However, in my experience, if I use outer join anywhere in > the query, I must use "JOIN" syntax to join all other tables as well, > otherwise, my query plans are _extremely_ slow. Postgres treats join syntax as an explicit definition of what order to joins in. So, I'd guess it sees the first as: do the LOJ and then join that to the separate table. And for right outer join (for example), those two queries would not be equivalent if I read the ordering correctly. The former syntax would mean outer first and then the inner, whereas the second would be inner first then the outer, and that could have different results. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] outer joins strangeness
It may be just me, or I am grossly misunderstanding syntax of outer joins, but I see that plans for my queries are different depending on how I place join conditions and sometimes even on order of the tables. Basically, if I mix ANSI-syntax outer joins (a left outer join b on a.id=b.id) and "where-syntax" joins (from a,b where a.id=b.id) in the same query, things get strange. Example: 1: explain select * from customers c,orders o left outer join adsl_orders ao on ao.order_id=o.order_id where c.cust_id=o.cust_id and c.cust_id=152 Nested Loop (cost=94.23..577.47 rows=2 width=290) -> Index Scan using customers_pkey on customers c (cost=0.00..2.02 rows=1 width=125) -> Materialize (cost=501.65..501.65 rows=5904 width=165) -> Hash Join (cost=94.23..501.65 rows=5904 width=165) -> Seq Scan on orders o (cost=0.00..131.04 rows=5904 width=58) -> Hash (cost=86.18..86.18 rows=3218 width=107) -> Seq Scan on adsl_orders ao (cost=0.00..86.18 rows=3218 width=107) Query 2: explain select * from customers c join orders o on c.cust_id=o.cust_id left outer join adsl_orders ao on ao.order_id=o.order_id where c.cust_id=152 Nested Loop (cost=0.00..9.30 rows=2 width=290) -> Nested Loop (cost=0.00..5.06 rows=2 width=183) -> Index Scan using customers_pkey on customers c (cost=0.00..2.02 rows=1 width=125) -> Index Scan using orders_idx1 on orders o (cost=0.00..3.03 rows=1 width=58) -> Index Scan using adsl_orders_pkey on adsl_orders ao (cost=0.00..2.02 rows=1 width=107) To me, both queries seem exactly identical in meaning, and should generate the same plans. However, in my experience, if I use outer join anywhere in the query, I must use "JOIN" syntax to join all other tables as well, otherwise, my query plans are _extremely_ slow. any hints? Or I am grossly misunderstanding outer join symantics? -alex ---(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] outer joins
Algirdas, This should do the trick: SELECT a.id,b.name FROM a,b WHERE a.id=b.id UNION SELECT id,null FROM a WHERE id NOT IN (SELECT id FROM b); Troy > > Hi all, > > I'm new to postgre, I've changed my work and consequently now i'm moving > from MS plaform. > In MS SQL there are such constructs left or right outer join, in postgres > there are no such thing > > Can You offer me strategy to make query that selects from table (a) and > joins to it another (b) > on e.g. a.id=b.aid but joins so that in case there is no value in table b > NULL is left in result: > in MS SQL i've used query: > > select a.id, b.name from a left outer join b on a.id=b.aid > > table a table b > > id | aid | name > --- > 11 | Tom > 23 | Sam > > result: > a.id | b.name > - > 1 | Tom > 2 | NULL > > thank you in advance > > Algirdas ©akmanas > IT manager > +370 99 90369 > [EMAIL PROTECTED] > Grafton Entertainment > http://www.tvnet.lt > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(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] outer joins
I've read that the version 7.1 provides outer join feature. Since I did not try it yet, I've no more information about it. > -Message d'origine- > De: Algirdas Sakmanas [SMTP:[EMAIL PROTECTED]] > Date: mercredi 4 avril 2001 13:03 > À:[EMAIL PROTECTED] > Objet:[SQL] outer joins > > Hi all, > > I'm new to postgre, I've changed my work and consequently now i'm moving > from MS plaform. > In MS SQL there are such constructs left or right outer join, in postgres > there are no such thing > > Can You offer me strategy to make query that selects from table (a) and > joins to it another (b) > on e.g. a.id=b.aid but joins so that in case there is no value in table b > NULL is left in result: > in MS SQL i've used query: > > select a.id, b.name from a left outer join b on a.id=b.aid > > table a table b > > id | aid | name > --- > 11 | Tom > 23 | Sam > > result: > a.id | b.name > - > 1 | Tom > 2 | NULL > > thank you in advance > > Algirdas Sakmanas > IT manager > +370 99 90369 > [EMAIL PROTECTED] > Grafton Entertainment > http://www.tvnet.lt > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] outer joins
Hello, I believe these are supported in 7.1 On Wed, 4 Apr 2001, [iso-8859-4] Algirdas ©akmanas wrote: >Hi all, > >I'm new to postgre, I've changed my work and consequently now i'm moving >from MS plaform. >In MS SQL there are such constructs left or right outer join, in postgres >there are no such thing > >Can You offer me strategy to make query that selects from table (a) and >joins to it another (b) >on e.g. a.id=b.aid but joins so that in case there is no value in table b >NULL is left in result: >in MS SQL i've used query: > >select a.id, b.name from a left outer join b on a.id=b.aid > >table a table b > >id | aid | name >--- >11 | Tom >23 | Sam > >result: >a.id | b.name >- >1 | Tom >2 | NULL > >thank you in advance > >Algirdas ©akmanas >IT manager >+370 99 90369 >[EMAIL PROTECTED] >Grafton Entertainment >http://www.tvnet.lt > > > >---(end of broadcast)--- >TIP 2: you can get off all lists at once with the unregister command >(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- -- CommandPrompt - http://www.commandprompt.com OpenDocs, LLC. - http://www.opendocs.org LinuxPorts - http://www.linuxports.com LDP - http://www.linuxdoc.org -- Instead of asking why a piece of software is using "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] outer joins
Hi all, I'm new to postgre, I've changed my work and consequently now i'm moving from MS plaform. In MS SQL there are such constructs left or right outer join, in postgres there are no such thing Can You offer me strategy to make query that selects from table (a) and joins to it another (b) on e.g. a.id=b.aid but joins so that in case there is no value in table b NULL is left in result: in MS SQL i've used query: select a.id, b.name from a left outer join b on a.id=b.aid table a table b id | aid | name --- 11 | Tom 23 | Sam result: a.id | b.name - 1 | Tom 2 | NULL thank you in advance Algirdas Šakmanas IT manager +370 99 90369 [EMAIL PROTECTED] Grafton Entertainment http://www.tvnet.lt ---(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] Outer Joins
Marc, Tom, > Good point. Frankly, if you have a relevant large population of data > (>10,000 rows) to test, I'd love to see comparative execution tests > between the two query structures. > > Fortunately, this will all soon become moot; Tom says that outer joins > have been stable in the 7.1 build for a while. Speaking of which, > when's the 7.1 "release"? Huh, huh? On second thought, couldn't we use some kind of EXCLUDES clause to expedite this? Tom? Further, it occurs to me that as in my query, you don't want to use "NOT IN" on *either* version. Instead, use "NOT EXISTS", which is much, much faster. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Outer Joins
Marc, > >This would work, but it would be *much* slower than a UNION query. "Not > >In" queries are perhaps the slowest you can run; see the earlier thread > >"Query Problem" for a discussion. UNION queries are, in fact, very fast > >... just awkward to code and manipulate. > > Why should this be slower since the UNION Query still has an identical not in clause? > This is far easier (for me) to read. Good point. Frankly, if you have a relevant large population of data (>10,000 rows) to test, I'd love to see comparative execution tests between the two query structures. Fortunately, this will all soon become moot; Tom says that outer joins have been stable in the 7.1 build for a while. Speaking of which, when's the 7.1 "release"? Huh, huh? -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Antw: [SQL] Outer Joins
>>> "Marc Rohloff" <[EMAIL PROTECTED]> 01.11.2000 09.02 Uhr >>> > > select a.col1, b.col2 from a,b > where a.col1 = b.col2 > or b.col2 is null > This query has nothing to do with an outer join. See the following example: table a c1 --- x y and table b c2 --- x Then an outer join gives: select a,c1, b.c2 from a left outer join b on a.c1 = b.c2 c1 | c2 x | x y | (null) but your query gives: select a.c1, b.c2 from a, b where a.c1 = b.c2 or b.c2 is null c1 | c2 x | x because there are no rows in table b with c2 is null - Gerhard
Re: [SQL] Outer Joins
>> select a.col1, b.col2 from a,b >> where a.col1 = b.col2 >>or a.col1 not in (select b.col2 from b) >This would work, but it would be *much* slower than a UNION query. "Not >In" queries are perhaps the slowest you can run; see the earlier thread >"Query Problem" for a discussion. UNION queries are, in fact, very fast >... just awkward to code and manipulate. Why should this be slower since the UNION Query still has an identical not in clause? This is far easier (for me) to read. Marc
Re: [SQL] Outer Joins
Marc, > select a.col1, b.col2 from a,b > where a.col1 = b.col2 >or b.col2 is null The above would select each record in a that matched a record in b, plus each record in b for every possible a where b.col2 was null - not where there was no match. > select a.col1, b.col2 from a,b > where a.col1 = b.col2 >or a.col1 not in (select b.col2 from b) This would work, but it would be *much* slower than a UNION query. "Not In" queries are perhaps the slowest you can run; see the earlier thread "Query Problem" for a discussion. UNION queries are, in fact, very fast ... just awkward to code and manipulate. -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Outer Joins
Marc, I did not look at your queries, but outer joins are supported in the latest development version of PostgreSQL, and will be supported in the upcoming 7.1 release of the software - beta due soon. Regards, Grant Marc Rohloff wrote: > I've been looking at the open-source databases for a project I am working on and >while reading about Postgres I saw that they do not support outer joins yet. I was >intrigued by their solution of using a union query. > > Something Like: > select a.col1, b.col2 from a,b where a.col1 = b.col2 > union > select a.col1, NULL from a where a.col1 not in (select b.col2 from b) > > But I was wondering if the following would work (it does in some other databases) > > select a.col1, b.col2 from a,b > where a.col1 = b.col2 >or b.col2 is null > > or maybe even > > select a.col1, b.col2 from a,b > where a.col1 = b.col2 >or a.col1 not in (select b.col2 from b) > > These would seem to be far more efficient than a union query > (I would try this but I don't have a Unix box at the moment to install PostgreSQL >on!) > > Marc Rohloff -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED]) Software Engineer Universal Computer Services Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421Johannesburg, South Africa
[SQL] Outer Joins
I've been looking at the open-source databases for a project I am working on and while reading about Postgres I saw that they do not support outer joins yet. I was intrigued by their solution of using a union query. Something Like: select a.col1, b.col2 from a,b where a.col1 = b.col2 union select a.col1, NULL from a where a.col1 not in (select b.col2 from b) But I was wondering if the following would work (it does in some other databases) select a.col1, b.col2 from a,b where a.col1 = b.col2 or b.col2 is null or maybe even select a.col1, b.col2 from a,b where a.col1 = b.col2 or a.col1 not in (select b.col2 from b) These would seem to be far more efficient than a union query (I would try this but I don't have a Unix box at the moment to install PostgreSQL on!) Marc Rohloff
