Re: Trouble with strange OUTER JOIN syntax
От | Farid Hajji |
---|---|
Тема | Re: Trouble with strange OUTER JOIN syntax |
Дата | |
Msg-id | 200105280011.f4S0BJ603525@suse-1.meta.net обсуждение исходный текст |
Ответ на | Re: Trouble with strange OUTER JOIN syntax (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
> 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.
В списке pgsql-general по дате отправления: