On 29/10/2019 15:20, Tom Lane wrote: > Vik Fearing <vik.fear...@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
