Thread: Trouble with strange OUTER JOIN syntax
I'm trying to port a program written in ESQL/C for Informix to PostgreSQL using ecpg [ecpg is a great helper here, despite its shortcomings]. Right now, I'm having a hard time with OUTER JOINs, even when using newest PostgreSQL-7.1.1. How would you translate the following SELECT statements with outer joins to PostgreSQL syntax? SELECT ... FROM tab1 alias_tab1, OUTER ot1 alias_ot1, OUTER ot2 alias_ot2 WHERE ... SELECT ... FROM tab1 alias_tab1, OUTER (ot1 alias_ot1, ot2 alias_ot2) WHERE ... It is even possible to have multiple tables specified at FROM before the OUTER keyword, like here: SELECT ... FROM tab1 alias_tab1, tab2 alias_tab2, OUTER ot1 alias_ot1, OUTER ot2 alias_ot2 WHERE ... SELECT FROM tab1 alias_tab1, tab2 alias_tab2, OUTER (ot1 alias_ot1, ot2 alias_ot2) WHERE ... Another situation is here (same table 'otboth' with two aliases): SELECT ... FROM tab1 alias_tab1, OUTER otboth alias1, OUTER otboth alias2 WHERE ... I didn't find any clue in Bruce's book nor in the postgreSQL docs/faq as of 7.1.1. If at all possible, I'd like to stick to OUTER JOINs because they are used extensively throughout the program. Thank you for your help, -Farid. -- Farid Hajji -- Unix Systems and Network Admin | Phone: +49-2131-67-555 Broicherdorfstr. 83, D-41564 Kaarst, Germany | farid.hajji@ob.kamp.net - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - One OS To Rule Them All And In The Darkness Bind Them... --Bill Gates.
Farid Hajji <farid.hajji@ob.kamp.net> writes: > How would you translate the following SELECT statements with > outer joins to PostgreSQL syntax? > SELECT ... > FROM tab1 alias_tab1, tab2 alias_tab2, > OUTER ot1 alias_ot1, > OUTER ot2 alias_ot2 > WHERE ... > SELECT > FROM tab1 alias_tab1, tab2 alias_tab2, > OUTER (ot1 alias_ot1, ot2 alias_ot2) > WHERE ... Tell me what that means, and I'll tell you how to translate it. What is being joined to what, on what keys, and which side is allowed to become null in the join? regards, tom lane
Hi Tom, > > How would you translate the following SELECT statements with > > outer joins to PostgreSQL syntax? > > > SELECT ... > > FROM tab1 alias_tab1, tab2 alias_tab2, > > OUTER ot1 alias_ot1, > > OUTER ot2 alias_ot2 > > WHERE ... > > > SELECT > > FROM tab1 alias_tab1, tab2 alias_tab2, > > OUTER (ot1 alias_ot1, ot2 alias_ot2) > > WHERE ... > > Tell me what that means, and I'll tell you how to translate it. > What is being joined to what, on what keys, and which side is > allowed to become null in the join? Having examined the statements in more detail, it seems to me like some kind of "multijoin" is required here: * outer-joining more than one table to a primary table: SELECT ... FROM tabmain, OUTER tab2, OUTER tab3 Here, I'll guess that both tab2 and tab3 are being outer-joined to tabmain. Therefore tab2 and tab3 columns are allowed to be null, whereas tabmain column's are not. * outer-joining one (or more than one) table to a cartesian product of other tables: SELECT ... FROM tab1, tab2, OUTER tab3 SELECT ... FROM tab1, tab2, OUTER (tab3, tab4) SELECT ... FROM tab1, tab2, OUTER tab3, OUTER tab4 In the first example, tab3 is being joind to the cartesian product (inner join) of both tab1 and tab2. Therefore tab1 x tab2 is not allowed to be null, whereas tab3 is allowed to be. The next examples seem to generalize this: two tables (tab3 and tab4) are being outer-joined to existing cartesian product tab1 x tab2. I'm not sure what the difference may be between: OUTER (tab3, tab4) and OUTER tab3, OUTER tab4. If PostgreSQL doesn't support this feature (yet?), I'll have to simulate it programatically. I just hoped to avoid the trouble of doing so, because the program I'm porting contains a lot of such "multijoins". Thanks for your help, -Farid. -- Farid Hajji -- Unix Systems and Network Admin | Phone: +49-2131-67-555 Broicherdorfstr. 83, D-41564 Kaarst, Germany | farid.hajji@ob.kamp.net - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - One OS To Rule Them All And In The Darkness Bind Them... --Bill Gates.
On Fri, May 25, 2001 at 05:24:18PM +0200, Farid Hajji wrote: > Hi Tom, > > > > How would you translate the following SELECT statements with > > > outer joins to PostgreSQL syntax? > > > > > SELECT ... > > > FROM tab1 alias_tab1, tab2 alias_tab2, > > > OUTER ot1 alias_ot1, > > > OUTER ot2 alias_ot2 > > > WHERE ... > > > > > SELECT > > > FROM tab1 alias_tab1, tab2 alias_tab2, > > > OUTER (ot1 alias_ot1, ot2 alias_ot2) > > > WHERE ... > > > > Tell me what that means, and I'll tell you how to translate it. > > What is being joined to what, on what keys, and which side is > > allowed to become null in the join? > Having examined the statements in more detail, it seems to me like > some kind of "multijoin" is required here: > > * outer-joining more than one table to a primary table: > SELECT ... FROM tabmain, OUTER tab2, OUTER tab3 > > Here, I'll guess that both tab2 and tab3 are being outer-joined > to tabmain. Therefore tab2 and tab3 columns are allowed to > be null, whereas tabmain column's are not. > > * outer-joining one (or more than one) table to a cartesian > product of other tables: > SELECT ... FROM tab1, tab2, OUTER tab3 > SELECT ... FROM tab1, tab2, OUTER (tab3, tab4) > SELECT ... FROM tab1, tab2, OUTER tab3, OUTER tab4 > > In the first example, tab3 is being joind to the cartesian product > (inner join) of both tab1 and tab2. Therefore tab1 x tab2 is not > allowed to be null, whereas tab3 is allowed to be. > > The next examples seem to generalize this: two tables (tab3 and tab4) > are being outer-joined to existing cartesian product tab1 x tab2. > I'm not sure what the difference may be between: > OUTER (tab3, tab4) > and > OUTER tab3, OUTER tab4. > > If PostgreSQL doesn't support this feature (yet?), I'll have to simulate > it programatically. I just hoped to avoid the trouble of doing so, > because the program I'm porting contains a lot of such "multijoins". How 'bout: SELECT * FROM foo LEFT JOIN bar ON foo.foo_id = bar.foo_id LEFT JOIN baz on bar.id = baz.bar_id; simple example output: foo_id | data | foo_id | bar_id | data | baz_id | bar_id | data --------+------+--------+--------+---------+--------+--------+------------- 1 | one | 1 | 1 | one-one | 1 | 1 | one-one-one 1 | one | 1 | 1 | one-one | 2 | 1 | one-one-two 1 | one | 1 | 2 | one-two | | | 2 | two | | | | | | (4 rows) "baz" has no candidate with "bar_id" = 2, and "bar" has no candidate where "foo_id" = 2. But it's not a product, as baz has a {3,3,'three-three-three'} tuple that won't join. You have to specify a join condition, or you get a product. -- Eric G. Miller <egm2@jps.net>
Farid Hajji <farid.hajji@ob.kamp.net> writes: > The next examples seem to generalize this: two tables (tab3 and tab4) > are being outer-joined to existing cartesian product tab1 x tab2. > I'm not sure what the difference may be between: > OUTER (tab3, tab4) > and > OUTER tab3, OUTER tab4. I suspect that these correspond to the standard syntaxes SELECT ... FROM (tab1 JOIN tab2 ON condition12) LEFT JOIN (tab3 JOIN tab4 ON condition34) ON condition1234; and SELECT ... FROM ((tab1 JOIN tab2 ON condition12) LEFT JOIN tab3 ON condition123) LEFT JOIN tab4 ON condition1234; respectively. In the first case, tab1 is inner-joined to tab2 and tab3 is separately inner-joined to tab4, then the results are outer-joined (with the tab3*tab4 product being the nullable side). In the second case, tab1 is inner-joined to tab2, then tab3 is outer-joined to this product, and finally tab4 is outer-joined to the result. Obviously these orderings can yield different results because of null-row addition (whereas it wouldn't really matter if all the joins were inner joins). Note that the standard syntax makes you attach a join condition (for example, "tab1.x = tab2.y") to each of these operations, rather than intuiting which parts of the WHERE clause are to be taken as the join condition. Again, this wouldn't matter for inner joins but it makes a big difference for outer joins. Example: select * from tab1 left join tab2 on (tab1.a = tab2.b and tab2.c = 0); is not at all the same as select * from tab1 left join tab2 on (tab1.a = tab2.b) where tab2.c = 0; The WHERE clause is not the join condition, but is applied after the join is done (and null rows are inserted). So, for example, if tab1 contains just A=1 and tab2 contains just B=1, C=2, the first case produces output 1,NULL,NULL because there are no tab2 rows that meet the outer-join condition with tab1's row. But the second case produces no output rows at all --- the outer-join produces 1,1,2 which is then removed by the WHERE filter. The standard's syntax is rather verbose and ugly, but it has the great virtue of handling outer joins unambiguously. None of the vendor- specific syntaxes I've seen are very clear about the implications of an outer join condition. BTW, you need PG 7.1 or later to work with outer joins. regards, tom lane
> > Having examined the statements in more detail, it seems to me like > > some kind of "multijoin" is required here: > > > > * outer-joining more than one table to a primary table: > > SELECT ... FROM tabmain, OUTER tab2, OUTER tab3 > > > > Here, I'll guess that both tab2 and tab3 are being outer-joined > > to tabmain. Therefore tab2 and tab3 columns are allowed to > > be null, whereas tabmain column's are not. > > > > * outer-joining one (or more than one) table to a cartesian > > product of other tables: > > SELECT ... FROM tab1, tab2, OUTER tab3 > > SELECT ... FROM tab1, tab2, OUTER (tab3, tab4) > > SELECT ... FROM tab1, tab2, OUTER tab3, OUTER tab4 > > > > In the first example, tab3 is being joind to the cartesian product > > (inner join) of both tab1 and tab2. Therefore tab1 x tab2 is not > > allowed to be null, whereas tab3 is allowed to be. > > > > The next examples seem to generalize this: two tables (tab3 and tab4) > > are being outer-joined to existing cartesian product tab1 x tab2. > > I'm not sure what the difference may be between: > > OUTER (tab3, tab4) > > and > > OUTER tab3, OUTER tab4. > > > > If PostgreSQL doesn't support this feature (yet?), I'll have to simulate > > it programatically. I just hoped to avoid the trouble of doing so, > > because the program I'm porting contains a lot of such "multijoins". > > How 'bout: > > SELECT * FROM foo LEFT JOIN bar ON foo.foo_id = bar.foo_id > LEFT JOIN baz on bar.id = baz.bar_id; > > simple example output: > > foo_id | data | foo_id | bar_id | data | baz_id | bar_id | data > --------+------+--------+--------+---------+--------+--------+------------- > 1 | one | 1 | 1 | one-one | 1 | 1 | one-one-one > 1 | one | 1 | 1 | one-one | 2 | 1 | one-one-two > 1 | one | 1 | 2 | one-two | | | > 2 | two | | | | | | > (4 rows) > > "baz" has no candidate with "bar_id" = 2, and "bar" has no candidate where > "foo_id" = 2. But it's not a product, as baz has a {3,3,'three-three-three'} > tuple that won't join. You have to specify a join condition, or you get a > product. Yes, chaining the outer join(s) did the trick for me here! > Eric G. Miller <egm2@jps.net> Many thanks, -Farid. -- Farid Hajji -- Unix Systems and Network Admin | Phone: +49-2131-67-555 Broicherdorfstr. 83, D-41564 Kaarst, Germany | farid.hajji@ob.kamp.net - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - One OS To Rule Them All And In The Darkness Bind Them... --Bill Gates.
> Farid Hajji <farid.hajji@ob.kamp.net> writes: > > The next examples seem to generalize this: two tables (tab3 and tab4) > > are being outer-joined to existing cartesian product tab1 x tab2. > > I'm not sure what the difference may be between: > > OUTER (tab3, tab4) > > and > > OUTER tab3, OUTER tab4. > > I suspect that these correspond to the standard syntaxes > > SELECT ... FROM > (tab1 JOIN tab2 ON condition12) > LEFT JOIN > (tab3 JOIN tab4 ON condition34) > ON condition1234; > > and > > SELECT ... FROM > ((tab1 JOIN tab2 ON condition12) > LEFT JOIN > tab3 ON condition123) > LEFT JOIN > tab4 ON condition1234; > > respectively. In the first case, tab1 is inner-joined to tab2 and > tab3 is separately inner-joined to tab4, then the results are > outer-joined (with the tab3*tab4 product being the nullable side). > In the second case, tab1 is inner-joined to tab2, then tab3 is > outer-joined to this product, and finally tab4 is outer-joined to > the result. Obviously these orderings can yield different results > because of null-row addition (whereas it wouldn't really matter if > all the joins were inner joins). Hmmm... yes, this could be so. I'll cross-check with Informix to make sure though. > Note that the standard syntax makes you attach a join condition > (for example, "tab1.x = tab2.y") to each of these operations, rather > than intuiting which parts of the WHERE clause are to be taken as the > join condition. Again, this wouldn't matter for inner joins but it > makes a big difference for outer joins. Example: > > select * from tab1 left join tab2 on (tab1.a = tab2.b and tab2.c = 0); > > is not at all the same as > > select * from tab1 left join tab2 on (tab1.a = tab2.b) where tab2.c = 0; > > The WHERE clause is not the join condition, but is applied after the > join is done (and null rows are inserted). So, for example, if tab1 > contains just A=1 and tab2 contains just B=1, C=2, the first case > produces output 1,NULL,NULL because there are no tab2 rows that meet > the outer-join condition with tab1's row. But the second case produces > no output rows at all --- the outer-join produces 1,1,2 which is then > removed by the WHERE filter. Ahh... that was the missing part in the puzzle. I was already wondering why my queries all yielded empty result sets. This was actually the exact reason! Many thanks for pointing this out. A section on multi-joins in the user's manual, that covers these issues would be rather useful, IMHO. > The standard's syntax is rather verbose and ugly, but it has the great > virtue of handling outer joins unambiguously. None of the vendor- > specific syntaxes I've seen are very clear about the implications of > an outer join condition. Indeed... > BTW, you need PG 7.1 or later to work with outer joins. I'm using 7.1.1 and it works fine now... > regards, tom lane Many thanks, -Farid. -- Farid Hajji -- Unix Systems and Network Admin | Phone: +49-2131-67-555 Broicherdorfstr. 83, D-41564 Kaarst, Germany | farid.hajji@ob.kamp.net - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - One OS To Rule Them All And In The Darkness Bind Them... --Bill Gates.