On Tue, 29 Oct 2019 at 07:05, Peter Eisentraut < peter.eisentr...@2ndquadrant.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.
