如何在> 2表上进行外部联接(Oracle) [英] How to do Outer Join on >2 Tables (Oracle)

问题描述

我不确定如何描述我的表结构，所以希望这有意义...

I'm not sure how to describe my table structure, so hope this makes sense...

我在层次关系中有3个表，因此A与B具有一对多的关系，而B与C具有一对多的关系.诀窍是B和C中的外键允许为空(即没有父定义).我也有D和E，与A，B或C(直接)无关.

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

最后，我有F，它是一个与C，D和E具有多对一关系的联接表.它的任何字段(其他表的FK)都不能为空.

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.

我想编写一条SQL语句，将所有表连接到一个结果集中.我知道我必须使用外部联接，因为我希望所有A都返回，而不管它是否在B中有子代，并且与B和C类似.

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.

问题一:我一直在查看ANSI外部联接语法(以前我仅使用Oracle(+)")，找不到外部联接超过2个表的示例.有人可以提供/指向示例吗?

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?

问题二:是否可以基于联接表F包含表D和E中的记录?如果是这样，这是通过外部联接完成的吗?

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?

谢谢！

编辑

当然，在我发布这篇文章后，我发现了一个回答问题1的示例.但是，问题2仍然让我感到困惑.

Of course, right after I post this, I found an example that answers question 1. However, question 2 still has me stumped.

示例:

         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

推荐答案

为澄清起见，大写字母表示表，小写字母表示主键/外键列.我可能应该写的类似于Quassnoi，但由于它是这样开始的，所以会继续写下去.

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.

此SQL返回我正在寻找的结果:

This SQL returns the results I'm loooking for:

         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

我试图像Bill那样设置我的SQL，但是使用FULL联接而不是LEFT联接，但是它没有返回与我相同的结果.我不能说我完全理解他的SQL，但是INNER联接过滤了一些结果.

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.

这篇关于如何在> 2表上进行外部联接(Oracle)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！