From: Manfred Koizar <mkoi-pg@aon.at>
To: Nicolas JOUANIN <n.jouanin@regie-france.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Let join syntax
Date: Mon, 16 Jun 2003 16:39:32 +0200
Message-ID: <8sjrev0b9ut9bcjgruh3p4onpg1d9qlqhd@4ax.com> (raw)
In-Reply-To: <CEEJJOCKHCPFNIOMMIDFGECICHAA.n.jouanin@regie-france.com>
References: <CEEJJOCKHCPFNIOMMIDFGECICHAA.n.jouanin@regie-france.com>
On Mon, 16 Jun 2003 12:34:34 +0200, "Nicolas JOUANIN"
<n.jouanin@regie-france.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mkoi-pg@aon.at, n.jouanin@regie-france.com
Subject: Re: Let join syntax
In-Reply-To: <8sjrev0b9ut9bcjgruh3p4onpg1d9qlqhd@4ax.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox