Re: [HACKERS] SQL outer join syntax

On Fri, 7 Jan 2000, Thomas Lockhart wrote:

> > > select ...
> > > from t1 inner join t4 on t1.x=t4.x,
> > > t2 left outer join t1
> > > on t2.y=t1.y and
> > > (t1.start_date between t2.start_date and t1.start_date),
> > > t3 left outer join t1 on t3.x=t1.x and t3.y = t1.y;
> > Let's be honest, folks. This is almost unreadable. I think we will
> > need some simpler way to access _outer_ in addition to the ANSI way.
>
> Nonsense! Especially since this isn't quite SQL92. Here is an SQL92
> query (I think ;) :
>
> select a, b, c
> from (t1 left join t2 using (x)) as j1 (a, b)
> right join t3 on (j1.a = t3.y);
>
> So you do a left join with t1 and t2, name the resulting intermediate
> table and columns, and then do a right join of the result with t3. I
> can't see other syntaxes being very much more obvious, particularly
> wrt predicting the actual result. Just because a query looks simpler
> doesn't necessarily mean that the syntax alway produces a more robust
> query.
>

This always strikes me as very much an each-to-his-own situation. I
generally prefer the oracle syntax myself; whilst there are potential
ambiguities (which oracle gets around by not executing ambiguous queries),
it's cleaner to write.

That said I don't particularly like SQL itself; if I wanted to program
COBOL I'd get a COBOL compiler:). The SQL92 syntax is more of an SQLism
than anything else, and the extra "english" words actually tend to obscure
the details of the join.

It certainly makes sense to use the SQL92 syntax; it is most important to
be compatible with the standards that anything else, but I would still
argue that a more straightforward syntax in parallel is
probably worthwhile.

> > I can't imagine how I would answer a question: "How do I do an ANSI
> > outer join". It would need its own FAQ page.
>
> Well, *you're* the one writing the book :))
>

I'd have thought this gave him justtification to complain about your
horrible syntax then:)

> - Thomas
>
> --
> Thomas Lockhart lockhart(at)alumni(dot)caltech(dot)edu
> South Pasadena, California
>

.............................Rod

+-----------------------------------------------------------------------------+
| Rod Chamberlin | rod(at)querix(dot)com Tel +44 1703 232345 |
| Software Engineer | Mob +44 7803 295406 |
| QueriX | Fax +44 1703 399685 |
+-----------------------------------------------------------------------------+
| The views expressed in this document do not necessarily represent those of |
| the management of QueriX (UK) Ltd. |
+-----------------------------------------------------------------------------+