Thread: Join Correlation Name
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); 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; Attached is a patch (based on 517bf2d910) adding this feature. -- Vik Fearing
Attachment
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); > > > 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; > > > Attached is a patch (based on 517bf2d910) adding this feature. Is this the same as https://commitfest.postgresql.org/25/2158/ ? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Tue, 29 Oct 2019 at 07:05, Peter Eisentraut <peter.eisentraut@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.
On 29/10/2019 12:05, Peter Eisentraut 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); >> >> >> 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; >> >> >> Attached is a patch (based on 517bf2d910) adding this feature. > > Is this the same as https://commitfest.postgresql.org/25/2158/ ? Crap. Yes, it is.
On 29/10/2019 12:24, Isaac Morland wrote: > 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 ... Yes, this is about having standard SQL syntax for that.
Vik Fearing <vik.fearing@2ndquadrant.com> writes:
> On 29/10/2019 12:24, Isaac Morland wrote:
>> 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 ...
> Yes, this is about having standard SQL syntax for that.
Please present an argument why this proposal is standard SQL syntax.
I see no support for it in the spec. AFAICS this proposal is just an
inconsistent wart; it makes it possible to write
(a join b using (z) as q) as t
and then what do you do? Moreover, why should you be able to
attach an alias to a USING join but not other sorts of joins?
After digging around in the spec for awhile, it seems like
there actually isn't any way to attach an alias to a join
per spec.
According to SQL:2011 7.6 <table reference>, you can attach an
AS clause to every variant of <table primary> *except* the
<parenthesized joined table> variant. And there's nothing
about AS clauses in 7.7 <joined table>, which is where it would
have to be mentioned if this proposal were spec-compliant.
What our grammar effectively does is to allow an AS clause to be
attached to <parenthesized joined table> as well, which seems
like the most natural thing to do if the committee ever decide
to rectify the shortcoming.
Anyway, we already have the functionality covered, and I don't
think we need another non-spec, non-orthogonal way to do it.
regards, tom lane
On 29/10/2019 15:20, Tom Lane wrote: > Vik Fearing <vik.fearing@2ndquadrant.com> writes: >> On 29/10/2019 12:24, Isaac Morland wrote: >>> 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 ... >> Yes, this is about having standard SQL syntax for that. > Please present an argument why this proposal is standard SQL syntax. Is quoting the spec good enough? SQL:2016 Part 2 Foundation Section 7.10 <joined table>: <join specification> ::= <join condition> | <named columns join> <join condition> ::= ON <search condition> <named columns join> ::= USING <left paren> <join column list> <right paren> [ AS <join correlation name> ] <join correlation name> ::= <correlation name> > I see no support for it in the spec. AFAICS this proposal is just an > inconsistent wart; it makes it possible to write > > (a join b using (z) as q) as t > > and then what do you do? Moreover, why should you be able to > attach an alias to a USING join but not other sorts of joins? I think possibly what the spec says (and that neither my patch nor Peter's implements) is assigning the alias just to the <join column list>. So my original example query should actually be: SELECT a.x, b.y, j.z FROM a INNER JOIN b USING (z) AS j; > After digging around in the spec for awhile, it seems like > there actually isn't any way to attach an alias to a join > per spec. > > According to SQL:2011 7.6 <table reference>, you can attach an > AS clause to every variant of <table primary> *except* the > <parenthesized joined table> variant. And there's nothing > about AS clauses in 7.7 <joined table>, which is where it would > have to be mentioned if this proposal were spec-compliant. > > What our grammar effectively does is to allow an AS clause to be > attached to <parenthesized joined table> as well, which seems > like the most natural thing to do if the committee ever decide > to rectify the shortcoming. > > Anyway, we already have the functionality covered, and I don't > think we need another non-spec, non-orthogonal way to do it. I think the issue here is you're looking at SQL:2011 whereas I am looking at SQL:2016. -- Vik Fearing
Bonjour Vik, > Is quoting the spec good enough? > SQL:2016 Part 2 Foundation Section 7.10 <joined table>: Ah, this is the one information I did not have when reviewing Peter's patch. > <named columns join> ::= > USING <left paren> <join column list> <right paren> [ AS <join correlation name> ] > > <join correlation name> ::= > <correlation name> > > I think possibly what the spec says (and that neither my patch nor > Peter's implements) is assigning the alias just to the <join column > list>. I think you are right, the alias is only on the identical columns. It solves the issue I raised about inaccessible attributes, and explains why it is only available with USING and no other join variants. > So my original example query should actually be: > > SELECT a.x, b.y, j.z FROM a INNER JOIN b USING (z) AS j; Yep, only z should be in j, it is really just about the USING clause. -- Fabien.
On 30/10/2019 09:04, Fabien COELHO wrote: > >> I think possibly what the spec says (and that neither my patch nor >> Peter's implements) is assigning the alias just to the <join column >> list>. > > I think you are right, the alias is only on the identical columns. > > It solves the issue I raised about inaccessible attributes, and > explains why it is only available with USING and no other join variants. > >> So my original example query should actually be: >> >> SELECT a.x, b.y, j.z FROM a INNER JOIN b USING (z) AS j; > > Yep, only z should be in j, it is really just about the USING clause. My reading of SQL:2016-2 7.10 SR 11.a convinces me that this is the case. My reading of transformFromClauseItem() convinces me that this is way over my head and I have to abandon it. :-(