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
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
