如何在 > 2 个表上进行外连接 (Oracle)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3721646/
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 do Outer Join on >2 Tables (Oracle)
提问by sdoca
I'm not sure how to describe my table structure, so hope this makes sense...
我不确定如何描述我的表结构，所以希望这是有道理的...
I have 3 tables in hierarchical relationship such that A has a one to many relationship to B which in turn has a one to many relationship with C. The trick is that the foreign key in B and C are allowed to be null (i.e. no parent defined). I also have D and E with no relation to A, B or C (directly).
我有 3 个层次关系的表，A 与 B 有一对多的关系，而 B 又与 C 有一对多的关系。诀窍是 B 和 C 中的外键允许为空（即没有父定义）。我也有 D 和 E，与 A、B 或 C（直接）无关。
Finally, I have F which is a join table with many to one relationships with C, D and E. None of its fields (FKs to the other tables) are nullable.
最后，我有 F，它是一个与 C、D 和 E 具有多对一关系的连接表。它的所有字段（其他表的 FK）都不能为空。
I would like to write a SQL statement that joins all the tables in a single result set. I know I have to user outer joins because I want all of A returned regardless of whether or not it has children in B and similar with B and C.
我想编写一个 SQL 语句，将所有表连接到一个结果集中。我知道我必须使用外部联接，因为我希望所有 A 都返回，无论它是否在 B 中有子级，并且与 B 和 C 相似。
Question One:I have been looking at the ANSI outer join syntax (I've only used Oracle "(+)" before) and cannot find an example that outer joins more than 2 tables. Can someone provide/point to an example?
问题一：我一直在看ANSI外连接语法（我之前只用过Oracle“(+)”），找不到外连接超过2个表的例子。有人可以提供/指出一个例子吗？
Question Two:Is it possible to include records from tables D and E based on the join table F? If so, is this done with outer joins?
问题二：是否可以基于连接表F包含表D和E的记录？如果是这样，这是通过外部联接完成的吗？
Thanks!
谢谢！
EDIT
编辑
Of course, right after I post this, I found an example that answers question 1. However, question 2 still has me stumped.
当然，在我发布这个之后，我发现了一个可以回答问题 1 的例子。然而，问题 2 仍然让我难倒。
Example:
例子：
SELECT A.a,
B.b,
C.c
FROM A
FULL OUTER JOIN B ON B.a = A.a
FULL OUTER JOIN C ON C.b = B.b
采纳答案by sdoca
For clarification, the uppercase letters referred to tables and the lowercase to the primary/foreign key columns. I probably should have written it similar to Quassnoi, but will keep with this since that's how it started.
为澄清起见，大写字母表示表，小写字母表示主键/外键列。我可能应该把它写成与 Quassnoi 类似的，但我会坚持下去，因为它是这样开始的。
This SQL returns the results I'm loooking for:
此 SQL 返回我正在寻找的结果：
SELECT A.a,
B.b,
C.c,
D.d,
E.e
FROM A
FULL OUTER JOIN B ON B.a = A.a
FULL OUTER JOIN C ON C.b = B.b
FULL OUTER JOIN F ON F.c = C.c
FULL OUTER JOIN D ON D.d = F.d
FULL OUTER JOIN E ON E.e = F.e
I tried to set up my SQL like Bill's but using FULL joins instead of LEFT ones, but it didn't return the same results as mine. I can't say that I fully understand his SQL, but the INNER joins filtered some of the results.
我尝试像 Bill 一样设置我的 SQL，但使用 FULL 连接而不是 LEFT 连接，但它没有返回与我相同的结果。我不能说我完全理解他的 SQL，但是 INNER 连接过滤了一些结果。
回答by Bill Karwin
So I visualize your schema like this:
所以我将你的架构形象化如下：
A --o< B --o< C --< F >-- D
>-- E
You can certainly do multiple joins, and you can also group join expressions with parentheses just like you can group arithmetic expressions.
您当然可以进行多个连接，也可以使用括号对连接表达式进行分组，就像对算术表达式进行分组一样。
SELECT ...
FROM A LEFT OUTER JOIN (
B LEFT OUTER JOIN (
C LEFT OUTER JOIN (
F INNER JOIN D ON D.d = F.d
INNER JOIN E ON E.e = F.e
) ON C.c = F.c
) ON B.b = C.b
) ON A.a = B.a
These parentheses are notsubqueries, they're just grouping join operations.
这些括号不是子查询，它们只是对连接操作进行分组。
回答by James Curran
select a.*, b.*, c.*
from a
left outer join b on a.b_id = b.id
left outer join c on a.c_id = c.id
Now, getting D, E & F in there gets trickier:
现在，将 D、E 和 F 放入其中变得更加棘手：
select c.*, d.*, e.*
from C
inner join f on c.id = f.c_id
inner join d on d.id = f.d_id
inner join e on d.id = f.e_id
Then we put it all together:
然后我们把它们放在一起：
select a.*, b.*, cde.*
from a
left outer join b on a.b_id = b.id
left outer join
(select c.id as c_id, c.*, d.*, e.*
from C
inner join f on c.id = f.c_id
inner join d on d.id = f.d_id
inner join e on d.id = f.e_id) CDE
on a.c_id = cde.c_id
回答by Quassnoi
SELECT a.*, b.*, c.*, d.*, e.*
FROM a
LEFT JOIN
b
ON b.a = a.id
LEFT JOIN
с
ON c.b = b.id
LEFT JOIN
f
ON f.с = c.id
LEFT JOIN
d
ON d.id = f.d
LEFT JOIN
e
ON e.id = f.e
