英文:
Short syntax for OUTER JOIN with one different column in the SELECT list
问题
在我的Postgres数据库中，我想要连接表A和表B以获取表C。表B被视为覆盖在表A上：
表A: "new"
| id | new | operation |
|---|---|---|
| 1 | newval1 | INSERT |
| 2 | newval2 | UPDATE |
表B "old"
| id | old | operation |
|---|---|---|
| 2 | oldval2 | UPDATE |
| 3 | oldval3 | DELETE |
表C "joined"
| id | new | old | operation |
|---|---|---|---|
| 1 | newval1 | NULL | INSERT |
| 2 | newval2 | oldval2 | UPDATE |
| 3 | NULL | oldval3 | DELETE |
SELECT
COALESCE(n.id, o.id), n.new, o.old,
COALESCE(n.operation, o.operation)
FROM
"new" n
FULL OUTER JOIN
old o ON n.id = o.id
我正在寻找更简短的语法。是否有更好的解决方案？
英文:
In my Postgres database, I would like to join table A and table B to get table C. Table B is treated like an overlay over table A:
Table A: "new"
| id | new | operation |
|---|---|---|
| 1 | newval1 | INSERT |
| 2 | newval2 | UPDATE |
Table B "old"
| id | old | operation |
|---|---|---|
| 2 | oldval2 | UPDATE |
| 3 | oldval3 | DELETE |
Table C "joined"
| id | new | old | operation |
|---|---|---|---|
| 1 | newval1 | NULL | INSERT |
| 2 | newval2 | oldval2 | UPDATE |
| 3 | NULL | oldval3 | DELETE |
SELECT
COALESCE(n.id, o.id), n.new, o.old,
COALESCE(n.operation, o.operation)
FROM
"new" n
FULL OUTER JOIN
old o ON n.id = o.id
I am looking for shorter syntax. Is there a better solution?
答案1
得分: 1
你可以使用USING子句执行某些操作：
SELECT id -- 这现在更简单
, n.new, o.old,
, COALESCE(n.operation, o.operation) AS operation -- 但不是这个
FROM new n
FULL OUTER JOIN old o USING (id); -- !
在USING子句中列出的列只在输出中出现一次，因此在SELECT列表中未经限定的id实际上替代了COALESCE(n.id, o.id) AS id。
连接更多表时必须谨慎，左侧的列名必须是明确的。在SELECT列表中未经限定的引用仅在没有其他表向右添加相同列名时才可能。这使得在以后修改查询时，USING子句更容易出现问题。因此请小心使用。
> ... JOIN USING的输出会抑制冗余列：没有必要打印匹配列的两个值，因为它们必须具有相同的值。而JOIN ON会生成所有来自***T1的列，然后是所有来自T2的列，JOIN USING生成列出的每个列对应的一个输出列（按列出的顺序），然后是来自T1和T2***的任何剩余列。
但其他列仍然需要COALESCE。并且对于这些列需要一个列别名（在你的示例中缺少）。
英文:
You can do something with the USING clause:
SELECT id -- this is simpler now
, n.new, o.old,
, COALESCE(n.operation, o.operation) AS operation -- but not this
FROM new n
FULL OUTER JOIN old o USING (id); -- !
Columns listed in the USING clause are only included in the output once, so an unqualified id in the SELECT list effectively replaces COALESCE(n.id, o.id) AS id.
Care must be taken when joining more tables: column names on the left must be unambiguous.
And the unqualified reference in the SELECT list is only possible if the same column name isn't added in another table to the right.
This makes the USING clause more susceptible to breakage when altering a query later. So use it carefully.
> ... the output of JOIN USING suppresses redundant columns: there is
> no need to print both of the matched columns, since they must have
> equal values. While JOIN ON produces all columns from T1
> followed by all columns from T2, JOIN USING produces one
> output column for each of the listed column pairs (in the listed
> order), followed by any remaining columns from T1, followed by
> any remaining columns from T2.
But you still need COALESCE for other columns.
And you need a column alias for those (missing in your example).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论