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(at)sea(dot)plala(dot)or(dot)jp>
> To: Michael Adler <adler(at)glimpser(dot)org>
> Cc: pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] FULL JOIN with 3 or more tables
>
> On Wed, 3 Apr 2002 15:58:55 -0500 (EST)
> Michael Adler <adler(at)glimpser(dot)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