On Tue, 2005-04-12 at 11:26 -0400, Ann W. Harrison wrote:

>
> Jason,
>
> Flattery will get you a long way - though Helen is the real expert
> in this area, in my opinion. The SQL-89 join syntax is OK if all your
> joins are inner joins and you never mix the syntaxes.
>
> In other words, this is old-fashioned but OK:
>
> select ... from t1 x, t2 y, t3 z
> where x.a = y.b and y.a = z. b
>
> this is not good:
>
> select ... from t1 x
> inner join t2 y, t3 z on x.a = y.b
> where y.a = z.b
>
> this is very bad:
>
> select ... from t1 x
> left outer join t2 y, t3 z on x.a = y.b
> where y.a = z.b
>
> and this is terrible:
>
> select ... from t1 x, t2 y
> left outer join t3 z on y.a = z.b
> where x.a = y.b
>
> The problem is that there is a required precedence between the SQL-89
> type join and the inner and outer SQL-92 joins that affects the
> optimizer's ability to distribute equalities and perform filtering on
> the input streams at the initial phases of the join. Those abilities
> are critical to good performance. The precedence can also trip up the
> logic of the query, leading to unexpected, but not inexplicable results.
>
> So, to answer your question, using the SQL-89 joins in program (or
> procedure) code is a lazy practice that shows a lack of willingness to
> learn and very little consideration for the people who will inherit the
> code. It also leads to poor performance and unexpected results.
>
> Personally, I no longer write SQL-89 joins except in interactive tools
> and change code that uses that join type as I find it.
>
>
> Is that clear?
>
>
> Cheers,
>
>
> Ann
>

To be fair, a lot depends on your shop.

In our shop, most of our programmers (about 150 to 200) get confused if
you use the full syntax. But then, most of them are COBOL'ers and use
the ISPF editor (ugh!). On the occasions that we do see outer joins, we
see a lot of your last example ("terrible").