Re: Trouble with strange OUTER JOIN syntax

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(dot)hajji(at)ob(dot)kamp(dot)net
- - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - -
One OS To Rule Them All And In The Darkness Bind Them... --Bill Gates.