Re: Join Correlation Name

On Tue, 29 Oct 2019 at 07:05, Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> On 2019-10-29 11:47, Vik Fearing wrote:
> > When joining tables with USING, the listed columns are merged and no
> > longer belong to either the left or the right side. That means they can
> > no longer be qualified which can often be an inconvenience.
> >
> >
> > SELECT a.x, b.y, z FROM a INNER JOIN b USING (z);
>

I'm confused. As far as I can tell you can qualify the join columns if you
want:

odyssey=> select exam_id, sitting_id, room_id, exam_exam_sitting.exam_id
from exam_exam_sitting join exam_exam_sitting_room using (exam_id,
sitting_id) limit 5;
exam_id | sitting_id | room_id | exam_id
---------+------------+---------+---------
22235 | 23235 | 22113 | 22235
22237 | 23237 | 22113 | 22237
23101 | 21101 | 22215 | 23101
23101 | 21101 | 22216 | 23101
23101 | 21101 | 22224 | 23101
(5 rows)

odyssey=>

In the case of a non-inner join it can make a difference whether you use
the left side, right side, or non-qualified version. If you need to refer
specifically to the non-qualified version in a different part of the query,
you can give an alias to the result of the join:

... (a join b using (z)) as t ...

> The SQL standard provides a workaround for this by allowing an alias on
> > the join clause. (<join correlation name> in section 7.10)
> >
> >
> > SELECT j.x, j.y, j.z FROM a INNER JOIN b USING (z) AS j;
>

What I would like is to be able to use both USING and ON in the same join;
I more often than I would like find myself saying things like ON ((l.a,
l.b, lc.) = (r.a, r.b, r.c) AND l.ab = r.bb). Also I would like to be able
to use and rename differently-named fields in a USING clause, something
like USING (a, b, c=d as f).

A bit of thought convinces me that these are both essentially syntactic
changes; I think it's already possible to represent these in the existing
internal representation, they just aren't supported by the parser.