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@jps.net>