Re: [HACKERS] Outer joins

> > Left outer joins will take the left-side table and null-fill entries
> > which do not have a corresponding match on the right-side table. If
> > your example is trying to get an output row for at least every input
> > row from t1, then perhaps the query would be
> > select * from t1 left join t2 using (x)
> >                  left join t3 using (x)
> >                  left join t4 using (x);
> > But since I haven't implemented it yet I don't have much experience
> > with the outer join syntax...
> You miss at least two points: The keyword OUTER and the column name
> from t1. As I know, LEFT is the default, so it could be omitted.

"OUTER" conveys no additional information, and can be omitted. My copy
of Date and Darwen indicates that "LEFT JOIN" is the minimum required
to get a left outer join (i.e. the "LEFT" can not be omitted).

I'm not sure what you mean about missing something about "the column
name for t1". My hypothetical query is referring to column "x",
present in all four tables. Was there some other place a column for t1
should be mentioned?

> Maybe
> SELECT * FROM t1 USING (X) OUTER JOIN t2 USING (Y)
>  OUTER JOIN t3 USING (Z)
>  OUTER JOIN t4 using (t);
> It should be possible to boil it down to
> SELECT * FROM t1 USING (X) OUTER JOIN t2 USING (Y), t3 USING (Z), t4 using (t);

This doesn't resemble SQL92, but may have some similarity to outer
join syntaxes in Oracle, Sybase, etc. Don't know myself.

A (hypothetical) simple two table outer join can be written as
 select * from t1 left join t2 using (x);

Introducing a third table to be "left outer joined" to this
intermediate result can be done as
 select * from t1 left join t2 using (x)                  left join t3 using (x);

where the second "x" refers to the column named "x" from the first
outer join, and the column named "x" from t3.

An alternate equivalent query would be
 select * from t1 left join t2 on t1.x = t2.x                  left join t3 on x = t3.x;

Hope this helps (and that I've got the details right now that I've
spouted off... :)
                      - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California