Re: Let join syntax

On Mon, 16 Jun 2003 12:34:34 +0200, "Nicolas JOUANIN"
<n(dot)jouanin(at)regie-france(dot)com> wrote:
>from
> coh x0 , cpy x1 ,bra x2 ,
> cur x3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND
>x5.usr_id=x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id,
> dpr x6 where ((((((((((x0.cpy_id = x1.cpy_id ) AND (x0.bra_id
> = x2.bra_id ) ) AND (x0.cur_id = x3.cur_id ) ) AND (x0.dpr_id
> = x6.dpr_id ) ) AND (x2.tad_id = x4.tad_id ) ) AND (x5.thr_id
> = x4.thr_id ) ) AND (x2.bra_id = x6.bra_id ) ) AND (x5.usr_id
> = x0.usr_id ) ) AND (x5.bra_id = x0.bra_id ) ) AND (x5.dpr_id
> = x0.dpr_id )

Nicolas, sometimes reformatting a query helps a lot:

FROM
coh x0 , cpy x1 ,bra x2 , cur x3 ,
tad x4 LEFT OUTER JOIN sec x5 ON
x5.thr_id = x4.thr_id AND
x5.usr_id = x0.usr_id AND
x5.bra_id = x0.bra_id AND
x5.dpr_id = x0.dpr_id,
dpr x6
WHERE
x0.cpy_id = x1.cpy_id AND
x0.bra_id = x2.bra_id AND
x0.cur_id = x3.cur_id AND
x0.dpr_id = x6.dpr_id AND
x2.tad_id = x4.tad_id AND
x2.bra_id = x6.bra_id AND
x5.thr_id = x4.thr_id AND
x5.usr_id = x0.usr_id AND
x5.bra_id = x0.bra_id AND
x5.dpr_id = x0.dpr_id

First note that the last four lines duplicate the ON conditions thus
effectively turning the OUTER JOIN into an INNER JOIN. As I suspect
that that was not your intention, simply omit those four conditions
from the WHERE clause.

Now inserting INNER JOIN where the syntax forces us to do so leads to
(completely untested):

FROM
coh x0
INNER JOIN bra x2 ON x0.bra_id = x2.bra_id
INNER JOIN tad x4 ON x2.tad_id = x4.tad_id
LEFT JOIN sec x5 ON x5.thr_id = x4.thr_id AND
x5.usr_id = x0.usr_id AND
x5.bra_id = x0.bra_id AND
x5.dpr_id = x0.dpr_id,
cpy x1, cur x3, dpr x6
WHERE
x0.cpy_id = x1.cpy_id AND
x0.cur_id = x3.cur_id AND
x0.dpr_id = x6.dpr_id AND
x2.bra_id = x6.bra_id

You might feel like replacing the remaining commas in the FROM clause
and the corresponding WHERE conditions with semantically equivalent
INNER JOINs. But this limits the freedom of the planner which may be
a good or a bad thing...

Servus
Manfred