Re: Trouble with strange OUTER JOIN syntax

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(at)jps(dot)net>