Re: FULL JOIN with 3 or more tables

Thanks for the link Masaru.

We're exploring a different design that will be more scalable and also
uses LEFT JOINs.



On Fri, 5 Apr 2002, Masaru Sugawara wrote:

> Date: Fri, 05 Apr 2002 00:01:46 +0900
> From: Masaru Sugawara <rk73@sea.plala.or.jp>
> To: Michael Adler <adler@glimpser.org>
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] FULL JOIN with 3 or more tables
>
> On Wed, 3 Apr 2002 15:58:55 -0500 (EST)
> Michael Adler <adler@glimpser.org> wrote:
>
>
> > I can do full joins just fine on two tables at a time:
> >
> > FROM t1 FULL JOIN t2 ON (t1.id = t2.id)
> >
> > Now, how would I full joint in a third table?
> >
> > FROM t1 FULL JOIN t2 ON (t1.id = t2.id) FULL JOIN t3 ON t1.id = t3.id
> >
> > That previous line would not join together two rows from t2 and t3.
>
>
> This topic is the same as the previous discussions(see the following URL).
> It seems to be still impossible to merge all the tables by that query.
>
>
http://groups.google.com/groups?hl=en&threadm=3507.1006111223%40sss.pgh.pa.us&rnum=1&prev=/groups%3Fq%3DCOALESCE(t1.name,t2.name)%26hl%3Den%26selm%3D3507.1006111223%2540sss.pgh.pa.us%26rnum%3D1
>
>
>
> But, if using a COALESCE(), you'll be able to merge.
>
> t1.id: 1,2,  4,5
> t2.id: 1,  3,4
> t3.id:   2,3,  5,6
>
> SELECT t1.id AS id1, t2.id AS id2, t3.id AS id3
>   FROM t1 FULL JOIN t2 ON (t1.id = t2.id)
>        FULL JOIN t3 ON (t1.id = t3.id);
>
>  id1 | id2 | id3
> -----+-----+-----
>    1 |   1 |
>    2 |     |   2
>      |   3 |
>      |     |   3
>    4 |   4 |
>    5 |     |   5
>      |     |   6
> (7 rows)
> explain analyze
> SELECT t.id1, t.id2 , t3.id AS id3
>   FROM (SELECT COALESCE(t1.id, t2.id) AS id12,
>                    t1.id AS id1, t2.id AS id2
>           FROM t1 FULL JOIN t2 ON (t1.id = t2.id)
>        ) AS t FULL JOIN t3 ON (t.id12 = t3.id);
>
>  id1 | id2 | id3
> -----+-----+-----
>    1 |   1 |
>    2 |     |   2
>      |   3 |   3    <-- being merged
>    4 |   4 |
>    5 |     |   5
>      |     |   6
> (6 rows)
>
>
> Regards,
> Masaru Sugawara
>
>

Mike