PL/SQL 使用多个左连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15872828/
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
PL/SQL Using multiple left join
提问by 4 Leave Cover
SELECT * FROM Table A LEFT JOIN TABLE B LEFT JOIN TABLE C
From the snippet above, TABLE C will left join into (TABLE B) or (data from TABLE A LEFT JOIN TABLE B) or (TABLE A)?
从上面的片段中，TABLE C 将左连接到 (TABLE B) 或 (来自 TABLE A LEFT JOIN TABLE B 的数据) 或 (TABLE A)？
采纳答案by Florin Ghita
TABLE C will left join into 1. (TABLE B) or 2. (data from TABLE A LEFT JOIN TABLE B) or 3. (TABLE A)?
表 C 将左联接成 1.（表 B）或 2.（来自表 A 左联接表 B 的数据）或 3.（表 A）？
The second. But The join codition will help you to understand more.
第二。但是加入条件将帮助您了解更多。
You can write:
你可以写：
SELECT *
FROM Table A
LEFT JOIN TABLE B ON (A.id = B.id)
LEFT JOIN TABLE C ON (A.ID = C.ID)
But you are able to:
但您可以：
SELECT *
FROM Table A
LEFT JOIN TABLE B ON (A.id = B.id)
LEFT JOIN TABLE C ON (A.id = C.id and B.code = C.code)
So, you can join on every field from previous tables and you join on "the result"(though the engine may choose it's way to get the result) of the previous joins.
因此，您可以加入先前表中的每个字段，并加入先前加入的“结果”（尽管引擎可能会选择获取结果的方式）。
Think at left join as non comutative operation (A left join Bis not the same as B left jon A) So, the order is important and C will be left joined at the previous joined tables.
将左连接视为非交换操作（A left join B与 不同B left jon A）因此，顺序很重要，C 将在之前的连接表中左连接。
回答by Gordon Linoff
The Oracle documentationis quite specific about how the joins are processed:
在Oracle文档是非常具体的关于如何处理连接：
To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result.
要执行三个或更多表的连接，Oracle 首先根据比较它们的列的连接条件连接两个表，然后根据包含连接表和新表的列的连接条件将结果连接到另一个表。Oracle 继续这个过程，直到所有表都加入到结果中。
This is the logic approach to handling the joins and is consistent with the ANSI standard (in other words, all database engines process the joins in order).
这是处理连接的逻辑方法，与 ANSI 标准一致（换句话说，所有数据库引擎都按顺序处理连接）。
However, when the query is actually executed, the optimizer may choose to run the joins in a different order. The result needs to be logically the same as processing the joins in the order given in the query.
但是，当实际执行查询时，优化器可能会选择以不同的顺序运行连接。结果需要在逻辑上与按照查询中给定的顺序处理连接相同。
Also, the join conditions may cause some unexpected conditions to arise. So if you have:
此外，连接条件可能会导致一些意外情况的出现。所以如果你有：
from A left outer join
B
on A.id = B.id left outer join
C
on B.id = C.id
Then, you might have the condition where Aand Ceach have a row with a particular id, but Bdoes not. With this formulation, you will not see the row in Cbecause it is joining to NULL. So, be careful with join conditions on left outer join, particularly when joining to a table other than the first table in the chain.
然后，您可能有条件 whereA和Ceach 都有一个具有特定 id 的行，但B没有。使用此公式，您将看不到行，C因为它连接到NULL。因此，请注意 上的连接条件left outer join，尤其是在连接到链中第一个表以外的表时。
回答by Sunny
You need to mentioned the column name properly in order to run the query. Let′s say if you are using:
您需要正确提及列名才能运行查询。假设您正在使用：
SELECT *
FROM Table A
LEFT JOIN TABLE B ON (A.id = B.id)
LEFT JOIN TABLE C ON (A.id = C.id and B.code = C.code)
Then you may get the following error:
那么你可能会得到以下错误：
ORA-00933:SQL command not properly ended.
ORA-00933:SQL 命令未正确结束。
So to avoid it you can try:
所以为了避免它，你可以尝试：
SELECT A.id as "Id_from_A", B.code as "Code_from_B"
FROM Table A
LEFT JOIN TABLE B ON (A.id = B.id)
LEFT JOIN TABLE C ON (A.id = C.id and B.code = C.code)
Thanks
谢谢
