作者
digoal
日期
2021-04-01
标签
PostgreSQL , SQL 标准 , join using alias
背景
SELECT ... FROM t1 JOIN t2 USING (a, b, c) AS x
x是一个小范围alias, 包含列 a,b,c. x.*, x, x.a, x.b, x.c 可以放在select或where中filter
```
Allow an alias to be attached to a JOIN ... USING
author Peter Eisentraut peter@eisentraut.org
Wed, 31 Mar 2021 15:09:24 +0000 (17:09 +0200)
committer Peter Eisentraut peter@eisentraut.org
Wed, 31 Mar 2021 15:10:50 +0000 (17:10 +0200)
commit 055fee7eb4dcc78e58672aef146334275e1cc40d
tree 2034e69c471453e9aea59712b09d3fed95bce330 tree
parent 27e1f14563cf982f1f4d71e21ef247866662a052 commit | diff
Allow an alias to be attached to a JOIN ... USING
This allows something like
SELECT ... FROM t1 JOIN t2 USING (a, b, c) AS x
x是一个小范围alias, 包含列 a,b,c. x.*, x, x.a, x.b, x.c 可以放在select或where中filter
where x has the columns a, b, c and unlike a regular alias it does not
hide the range variables of the tables being joined t1 and t2.
Per SQL:2016 feature F404 "Range variable for common column names".
Reviewed-by: Vik Fearing vik.fearing@2ndquadrant.com
Reviewed-by: Tom Lane tgl@sss.pgh.pa.us
Discussion: https://www.postgresql.org/message-id/flat/454638cf-d563-ab76-a585-2564428062af@2ndquadrant.com
```
+-- test join using aliases
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) WHERE J1_TBL.t = 'one'; -- ok
+ i | j | t | k
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one'; -- ok
+ i | j | t | k
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t = 'one'; -- error
+ERROR: invalid reference to FROM-clause entry for table "j1_tbl"
+LINE 1: ... * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t =...
+ ^
+HINT: There is an entry for table "j1_tbl", but it cannot be referenced from this part of the query.
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.i = 1; -- ok
+ i | j | t | k
+---+---+-----+----
+ 1 | 4 | one | -1
+(1 row)
+
+SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one'; -- error
+ERROR: column x.t does not exist
+LINE 1: ...CT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one...
+ ^
+SELECT * FROM (J1_TBL JOIN J2_TBL USING (i) AS x) AS xx WHERE x.i = 1; -- error (XXX could use better hint)
+ERROR: missing FROM-clause entry for table "x"
+LINE 1: ...ROM (J1_TBL JOIN J2_TBL USING (i) AS x) AS xx WHERE x.i = 1;
+ ^
+SELECT * FROM J1_TBL a1 JOIN J2_TBL a2 USING (i) AS a1; -- error
+ERROR: table name "a1" specified more than once
+SELECT x.* FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';
+ i
+---
+ 1
+(1 row)
+
+SELECT ROW(x.*) FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';
+ row
+-----
+ (1)
+(1 row)
+
+SELECT row_to_json(x.*) FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one';
+ row_to_json
+-------------
+ {"i":1}
+(1 row)
+
+ <para>
+ If a <replaceable class="parameter">join_using_alias</replaceable>
+ name is specified, it provides a table alias for the join columns.
+ Only the join columns listed in the <literal>USING</literal> clause
+ are addressable by this name. Unlike a regular <replaceable
+ class="parameter">alias</replaceable>, this does not hide the names of
+ the joined tables from the rest of the query. Also unlike a regular
+ <replaceable class="parameter">alias</replaceable>, you cannot write a
+ column alias list — the output names of the join columns are the
+ same as they appear in the <literal>USING</literal> list.
+ </para>
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
