Re: Join Correlation Name

On 29/10/2019 15:20, Tom Lane wrote:
> Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)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