postgresql 如何使用外连接和笛卡尔积连接三个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5791756/
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
How to join three tables with outer join and cartesian product
提问by 23tux
I have three tables in a PostgreSQL DB: Users, Activities, Venues.
我在 PostgreSQL 数据库中有三个表：用户、活动、场所。
Users:
- id
- name
Visits:
- id
- user_id
- location_id
Venues:
- id
- name
I want to retrieve all activities for a specific user, event if the user haven't visited a location yet. I've tried it with some joins:
我想检索特定用户的所有活动，如果用户尚未访问某个位置，则为事件。我已经尝试了一些连接：
SELECT venues.id as venue, COUNT(activities.id) as visits
FROM users
RIGHT OUTER JOIN activities ON users.id=activities.user_id
RIGHT OUTER JOIN venues ON activities.venue_id=venues.id
WHERE users.id=1234
GROUP BY venues.id
ORDER BY venues.id
I want the visits variable to contain 0 when the user haven't visited a location (= there is no entry in activities) and the count of activities if the user have visited the location.
我希望访问变量在用户未访问过某个位置时包含 0（= 活动中没有条目）以及如果用户访问过该位置的活动计数。
But I only get the locations that the user have visited:
但我只得到用户访问过的位置：
venue | visits
1 3
2 4
3 22
4 1
I thought, that the RIGHT OUTER JOINwould return all entries from the right side, but it doesn't.
我想，这RIGHT OUTER JOIN会从右侧返回所有条目，但事实并非如此。
回答by Paul Sasik
A LEFT OUTER JOIN means that you want all of the entities from the left-hand table whether or not the JOIN condition (or predicate) is satisfied.
LEFT OUTER JOIN 意味着无论是否满足 JOIN 条件（或谓词），您都需要左侧表中的所有实体。
But this is also one of those rare cases where a Cartesian product (CROSS JOIN) is actually necessary since you want to see every venue for every user. Try using the CROSS JOINlike this:
但这也是真正需要笛卡尔积（CROSS JOIN）的罕见情况之一，因为您希望看到每个用户的每个场所。尝试像这样使用CROSS JOIN：
SELECT v.id as venue, COUNT(a.id) as visits
FROM users AS u
CROSS JOIN venues as v --<-- CROSS JOIN for Cartesian product between users and venues
LEFT JOIN activities AS a ON u.id = a.user_id AND a.venue_id = v.id
WHERE u.id = 1234
GROUP BY v.id
ORDER BY v.id
回答by Ben
Check this out:
看一下这个：
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj57522.html
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj57522.html
Basic join explanations-- they can be confusing. I think you either want to switch the columns in the first join, or use left outer join instead.
基本的连接解释——它们可能会令人困惑。我认为您要么想在第一个连接中切换列，要么改用左外连接。
