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.