oracle 加入父表和子表的最佳方式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18126255/
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
Best way to join parent and child tables
提问by user1706047
I have Parent table A.
Ahas few child tables such as B,C,D,E,F,GThe child tables are not linked to each other. They are only linked to A.
Ahas a key Idwhich is used as foreign key in all the child tables.
我有父表A。
A有几个子表，如B，C，D，E，F，G子表不相互链接。它们只链接到A.
A有一个Id在所有子表中用作外键的键。
What should be the best way to join these tables so I can create a single view on this?
加入这些表的最佳方法应该是什么，以便我可以创建一个单一的视图？
回答by Giannis Paraskevopoulos
Since a parent may have a child row in some of those tables you must use LEFT OUTER JOIN.
由于父级在其中一些表中可能有子行，因此您必须使用 LEFT OUTER JOIN。
LEFT OUTER JOIN joins two tables returning all the rows of the LEFT table, in this case A and all the matches from the other tables. When there is no match it will return NULL in the corresponding columns of the tables that there was no match.
LEFT OUTER JOIN 连接两个表，返回 LEFT 表的所有行，在本例中为 A 以及来自其他表的所有匹配项。当没有匹配时，它将在没有匹配的表的相应列中返回 NULL。
SELECT *
FROM A
LEFT OUTER JOIN B
ON A.Id = B.ParentID
LEFT OUTER JOIN C
ON A.Id = C.ParentID
LEFT OUTER JOIN P
ON C.Id = P.ParentID
LEFT OUTER JOIN Q
ON C.Id = Q.ParentID
LEFT OUTER JOIN D
ON A.Id = D.ParentID
LEFT OUTER JOIN E
ON A.Id = E.ParentID
LEFT OUTER JOIN F
ON A.Id = F.ParentID
LEFT OUTER JOIN X
ON F.Id = X.ParentID
LEFT OUTER JOIN Y
ON F.Id = Y.ParentID
LEFT OUTER JOIN G
ON A.Id = G.ParentID
EDIT
编辑
I have added a way to add subchilds. I have intented them more just to make them obvious in a visual representation. But beware...if this lead to subchildren have other subchildren etc maybe your structure is not optimal.
我添加了一种添加子子项的方法。我的意图更多只是为了使它们在视觉表示中更加明显。但要注意......如果这导致子子节点有其他子子节点等，那么你的结构可能不是最佳的。
回答by t-clausen.dk
select <wanted columns>
from a
left join b
on a.id = b.a_id
left join c
on a.id = c.a_id
left join d
on a.id = d.a_id
