SQL 在查询中连接复合外键/主键列上的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21791478/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join tables on columns of composite foreign / primary key in a query
提问by samol
CREATE TABLE subscription (
magazine_id bigint,
user_id bigint,
PRIMARY KEY (magazine_id, user_id)
);
CREATE TABLE delivery (
magazine_id bigint,
user_id bigint,
FOREIGN KEY (subscription) REFERENCES subscription (magazine_id, user_id)
);
What is a good way to query for deliveries given a particular subscription? Is there a way to assign a column name to PRIMARY KEY (magazine_id, user_id)and the corresponding foreign key so that I can query like this:
在给定特定订阅的情况下查询交付的好方法是什么？有没有办法将列名分配给PRIMARY KEY (magazine_id, user_id)相应的外键，以便我可以这样查询：
SELECT *
FROM subscription
JOIN delivery ON (delivery.subscription_fk = delivery.subscription_pk);
Note: I can write something like this:
注意：我可以这样写：
SELECT *
FROM subscription
JOIN delivery ON (delivery.magazine_id = subscription.magazine_id
AND delivery.user_id = subscription.user_id);
However, I am under the impression that there is a less verbose way to achieve this.
但是，我的印象是有一种不那么冗长的方法来实现这一点。
回答by Erwin Brandstetter
There is a NATURAL JOIN:
有一个NATURAL JOIN：
SELECT *
FROM subscription
NATURAL JOIN delivery;
Quoting the manual on SELECT:
引用手册SELECT：
NATURAL
NATURALis shorthand for aUSINGlist that mentions all columns in the two tables that have the same names.
NATURAL
NATURAL是USING列出两个表中具有相同名称的所有列的列表的简写。
It would work for your test setup, but it's not strictly doing what you ask for. The connection is based on all columns sharing the same name. Foreign keys are not considered. The cases where NATURAL JOINis a good idea are few and far between.
它适用于您的测试设置，但它并没有严格按照您的要求进行。连接基于共享相同名称的所有列。不考虑外键。其中，这些案件NATURAL JOIN是一个好主意是少之又少。
Simplify code / less verbose
简化代码/不那么冗长
For starters, you could use table aliases and you don't need parentheses around the join conditions with ON(unlike with USING):
对于初学者，您可以使用表别名，并且不需要在连接条件周围加上括号 with ON（与 with 不同USING）：
SELECT *
FROM subscription s
JOIN delivery d ON d.magazine_id = s.magazine_id
AND d.user_id = s.user_id;
Since column names in the join conditions are identical, you can further simplify with USING:
由于连接条件中的列名相同，您可以进一步简化USING：
SELECT *
FROM subscription s
JOIN delivery d USING (magazine_id, user_id);
There is no syntax variant making joins based on foreign key constraints automatically. You would have to query the system catalogs and build the SQL dynamically.
没有自动基于外键约束进行连接的语法变体。您必须查询系统目录并动态构建 SQL。
回答by Smutje
Doesn't deliveryhas two columns representing the foreign key? Then it should work like with a non-composite primary key SELECT * FROM subscription JOIN delivery ON (delivery.magazine_id = subscription.magazine_id AND delivery.user_id = subscription.user_id).
没有delivery代表外键的两列吗？然后它应该像使用非复合主键一样工作SELECT * FROM subscription JOIN delivery ON (delivery.magazine_id = subscription.magazine_id AND delivery.user_id = subscription.user_id)。
