在查询中联接组合外键/主键的列上的表 [英] Join tables on columns of composite foreign / primary key in a query

问题描述

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

在给定特定订阅的情况下，查询交付的一种好方法是什么?有没有一种方法可以将列名分配给PRIMARY KEY (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

SELECT *
FROM subscription
JOIN delivery ON (delivery.subscription_fk = delivery.subscription_pk)

注意:我可以这样写:

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.

推荐答案

有一个NATURAL JOIN:

SELECT *
FROM subscription NATURAL JOIN delivery

引用关于SELECT 的手册:

Quoting the manual on SELECT:

NATURAL

NATURAL是USING列表的简写，该列表提到两个表中具有相同名称的所有列.

NATURAL is shorthand for a USING list that mentions all columns in the two tables that have the same names.

它可以用于您的测试设置，但是并非严格按照您的要求进行.该连接基于共享相同名称的所有列.不考虑外键.

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.

对于初学者来说，您可以使用表别名，并且不需要使用ON包围连接条件的括号(与USING不同)

For starters, you could use table aliases and you don't need parentheses around the join conditions with ON (unlike with USING):

SELECT *
FROM   subscription s
JOIN   delivery     d ON d.magazine_id = s.magazine_id
                     AND d.user_id = s.user_id;

由于联接条件中的列名相同，因此可以使用USING进一步简化:

Since column names in the join conditions are identical, you can further simplify with USING:

SELECT *
FROM   subscription s
JOIN   delivery     d USING (magazine_id, user_id);

没有语法变量会自动基于外键约束进行联接.您将不得不查询系统目录并为此动态创建SQL ...

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 for that ...

这篇关于在查询中联接组合外键/主键的列上的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！