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
Attachments:
join_correlation_name.v01.patchtext/x-patch; name=join_correlation_name.v01.patchDownload
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 22252556be..e4b17698d9 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -248,7 +248,7 @@ FROM <replaceable>table_reference</replaceable> <optional>, <replaceable>table_r
<listitem>
<synopsis>
<replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> ON <replaceable>boolean_expression</replaceable>
-<replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> USING ( <replaceable>join column list</replaceable> )
+<replaceable>T1</replaceable> { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable> USING ( <replaceable>join column list</replaceable> ) [ AS <replaceable>alias</replaceable> ]
<replaceable>T1</replaceable> NATURAL { <optional>INNER</optional> | { LEFT | RIGHT | FULL } <optional>OUTER</optional> } JOIN <replaceable>T2</replaceable>
</synopsis>
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 06d611b64c..fcb474aaee 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -620,7 +620,7 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
</varlistentry>
<varlistentry>
- <term><literal>USING ( <replaceable class="parameter">join_column</replaceable> [, ...] )</literal></term>
+ <term><literal>USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) [ AS <replaceable>alias</replaceable> ]</literal></term>
<listitem>
<para>
A clause of the form <literal>USING ( a, b, ... )</literal> is
@@ -629,6 +629,8 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
<literal>USING</literal> implies that only one of each pair of
equivalent columns will be included in the join output, not
both.
+ An alias may be provided to reference these columns. In this
+ case, the <literal>AS</literal> keyword is required.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 3f67aaf30e..34a4e0fa57 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -12004,7 +12004,11 @@ joined_table:
n->larg = $1;
n->rarg = $4;
if ($5 != NULL && IsA($5, List))
- n->usingClause = (List *) $5; /* USING clause */
+ {
+ /* USING clause */
+ n->usingClause = linitial_node(List, castNode(List, $5));
+ n->alias = lsecond_node(Alias, castNode(List, $5));
+ }
else
n->quals = $5; /* ON clause */
$$ = n;
@@ -12018,7 +12022,11 @@ joined_table:
n->larg = $1;
n->rarg = $3;
if ($4 != NULL && IsA($4, List))
- n->usingClause = (List *) $4; /* USING clause */
+ {
+ /* USING clause */
+ n->usingClause = linitial_node(List, castNode(List, $4));
+ n->alias = lsecond_node(Alias, castNode(List, $4));
+ }
else
n->quals = $4; /* ON clause */
$$ = n;
@@ -12126,9 +12134,14 @@ join_outer: OUTER_P { $$ = NULL; }
* ON expr allows more general qualifications.
*
* We return USING as a List node, while an ON-expr will not be a List.
+ *
+ * Since the USING clause merges the columns, they no longer belong to either
+ * the left or the right table. SQL allows an alias to be assigned to the JOIN
+ * so that the columns can be qualified.
*/
-join_qual: USING '(' name_list ')' { $$ = (Node *) $3; }
+join_qual: USING '(' name_list ')' { $$ = (Node *) (list_make2($3, NULL)); }
+ | USING '(' name_list ')' AS ColId { $$ = (Node *) (list_make2($3, makeAlias($6, NIL))); }
| ON a_expr { $$ = $2; }
;
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index b58d560163..9694688077 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -1621,6 +1621,30 @@ SELECT '' AS "xxx", *
| 4 | 1 | one | 2
(4 rows)
+-- Test naming the join result, first with postgres syntax...
+SELECT '' AS "xxx", *
+ FROM (J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (d, b) USING (b)) AS j
+ ORDER BY j.b, j.c, j.d;
+ xxx | b | a | c | d
+-----+---+---+-------+---
+ | 0 | 5 | five |
+ | 0 | | zero |
+ | 2 | 3 | three | 2
+ | 4 | 1 | one | 2
+(4 rows)
+
+-- ...then with standard SQL syntax.
+SELECT '' AS "xxx", *
+ FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (d, b) USING (b) AS j
+ ORDER BY j.b, j.c, j.d;
+ xxx | b | a | c | d
+-----+---+---+-------+---
+ | 0 | 5 | five |
+ | 0 | | zero |
+ | 2 | 3 | three | 2
+ | 4 | 1 | one | 2
+(4 rows)
+
--
-- NATURAL JOIN
-- Inner equi-join on all columns with the same name
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 57481d0411..aa8a5771c5 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -126,6 +126,16 @@ SELECT '' AS "xxx", *
FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b)
ORDER BY b, t1.a;
+-- Test naming the join result, first with postgres syntax...
+SELECT '' AS "xxx", *
+ FROM (J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (d, b) USING (b)) AS j
+ ORDER BY j.b, j.c, j.d;
+
+-- ...then with standard SQL syntax.
+SELECT '' AS "xxx", *
+ FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (d, b) USING (b) AS j
+ ORDER BY j.b, j.c, j.d;
+
--
-- NATURAL JOIN
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. :-(