oracle 有没有办法在oracle中进行多个左外连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1614731/
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
is there a way to do multiple left outer joins in oracle?
提问by Omnipresent
Why won't this work in Oracle?
为什么这在 Oracle 中不起作用？
Is there a way to make this work?
有没有办法使这项工作？
FROM table1 a,
table2 b,
table3 c
WHERE a.some_id = '10'
AND a.other_id (+)= b.other_id
AND a.other_id (+)= c.other_id
I want table1to be left outer joined on multiple tables...
我想table1在多个表上保持外部连接...
If I try to change it to use an ANSI join, I get compilation errors. I did the following:
如果我尝试将其更改为使用 ANSI 连接，则会出现编译错误。我做了以下事情：
FROM table2 b, table3 c
LEFT JOIN table1 a ON a.other_id = b.other_id and a.other_id = c.other_id
回答by kdgregory
OK, looking at the examplesfrom the Oracle docs, my recollection of the syntax was correct, so I'm turning my comment into an answer. Assuming that your goal is a left outer join where A is the base table, and you join matching rows from B and C, rewrite your query as follows (note that I'm just changing the prefixes; I like to have the source rowset on the right).
好的，查看Oracle 文档中的示例，我对语法的回忆是正确的，所以我将我的评论变成了答案。假设您的目标是左外连接，其中 A 是基表，并且您连接来自 B 和 C 的匹配行，请按如下方式重写您的查询（请注意，我只是更改前缀；我喜欢将源行集放在正确的）。
FROM table1 a,
table2 b,
table3 c
WHERE a.some_id = '10'
AND b.other_id (+)= a.other_id
AND c.other_id (+)= a.other_id
If that's not what you're trying to do, then the query is borked: you're doing a cartesian join of B and C, and then attempting an outer join from that partial result to A, with an additional predicate on A. Which doesn't make a lot of sense.
如果这不是您要执行的操作，那么查询就会停止：您正在执行 B 和 C 的笛卡尔连接，然后尝试从该部分结果到 A 的外部连接，并在 A 上附加一个谓词。其中没有多大意义。
回答by user188658
use ansi joins. They are way clearer IMO. BUt for some reason they don't work with materialized views...
使用ansi连接。他们是更清晰的 IMO。但是出于某种原因，它们不适用于物化视图......
回答by Henry Gao
You can do something like this.
你可以做这样的事情。
FROM table1 a, table2 b, table3 c
WHERE a.some_id = '10'
AND a.other_id = b.other_id(+)
AND a.other_id = c.other_id(+)
回答by Dave Costa
I wanted to address separately this part of your question:
我想单独解决您问题的这一部分：
If I try to change it to ANSI join I get compilation errors. I did the following:
FROM table2 b, table3 c
LEFT JOIN table1 a ON a.other_id = b.other_id and a.other_id = c.other_id
In an ANSI join, at least in Oracle, you are operating on exactly two row sources. The LEFT JOIN operator in your example has table3 and table1 as its operands; so you cannot reference "b.otherid" in the ON clause. You need a new join operator for each additional table.
在 ANSI 连接中，至少在 Oracle 中，您正在对两个行源进行操作。您示例中的 LEFT JOIN 运算符将 table3 和 table1 作为其操作数；所以你不能在 ON 子句中引用“b.otherid”。每个附加表都需要一个新的连接运算符。
I believe what you are trying to do is outer join table 2 and table 3 to table 1. So what you should be doing is this:
我相信你要做的是将表 2 和表 3 外连接到表 1。所以你应该做的是：
FROM table1 a LEFT JOIN table2 b ON b.other_id = a.other_id
LEFT JOIN table3 c ON c.other_id = a.other_id
or Henry Gao's query if you want to use Oracle-specific syntax.
如果您想使用特定于 Oracle 的语法，或者 Henry Gao 的查询。
回答by northpole
In oracle you cannot outer join the same table to more than one other table. You can create views that have joins in them, then join to that view. As side note, you also cannot outer join to an sub select, so that is not an option here either.
在 oracle 中，您不能将同一个表外部连接到多个其他表。您可以创建具有连接的视图，然后连接到该视图。作为旁注，您也不能外部连接到子选择，因此这也不是这里的选项。
回答by Jeroen
You could off course try the following (Table b and c being the BASE) FROM (SELECT other_id FROM table2 UNION SELECT other_id FROM table3) b LEFT JOIN table1 a b.other_id = a.other_id
您当然可以尝试以下（表 b 和 c 是基础） FROM (SELECT other_id FROM table2 UNION SELECT other_id FROM table3) b LEFT JOIN table1 a b.other_id = a.other_id
But then again I am an Oracle Nono
但话又说回来，我是一个 Oracle Nono
