oracle 多个表上的多个 LEFT OUTER JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48750345/
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
Multiple LEFT OUTER JOIN on multiple tables
提问by Platus
I would like to convert the following Oracle SQL query syntax (Use LEFT OUTER JOINinstead of (+)):
我想转换以下 Oracle SQL 查询语法（使用LEFT OUTER JOIN而不是(+)）：
SELECT *
FROM TABLEA A, TABLEB B, TABLEC C, TABLED D
WHERE MY_COL = @col_val
AND A.X = B.X(+)
AND B.Y = C.Y(+)
AND D.Z=A.Z
Here is what I tried so far:
这是我到目前为止尝试过的：
SELECT *
FROM TABLEA A, TABLEB B, TABLEC C, TABLED D
LEFT OUTER JOIN TABLEA A ON A.X = B.X
LEFT OUTER JOIN TABLEC C ON B.Y = C.Y
WHERE MY_COL = @col_val
AND D.Z = A.Z;
But I get the error :
但我收到错误：
"ORA-00904: "B"."X" : invalid identifier"
"ORA-00904: "B"."X": 无效标识符"
采纳答案by Gordon Linoff
The joinon Dis an inner join, the rest are left outer joins:
该join上D是内连接外，其余均为左外连接：
SELECT *
FROM TABLEA A JOIN
TABLED D
ON D.Z = A.Z LEFT JOIN
TABLEB B
ON A.X = B.X LEFT JOIN
TABLEC C
ON B.Y = C.Y
WHERE MY_COL = @col_val;
I always start chains of joins with inner joins followed by the left outer join. I never use right join, and full joinrather rarely. The inner joins define the rows in the result set, so they come first.
我总是从内部连接开始连接链，然后是left outer join. 我从不使用right join，而且full join很少使用。内连接定义了结果集中的行，因此它们排在最前面。
回答by scaisEdge
You don't should mix explicit and implicit sintax
你不应该混合显式和隐式语法
SELECT *
FROM TABLEA A
INNER JOIN TABLEL L ON L.Z = A.Z
LEFT OUTER JOIN TABLEB B ON A.X = B.X
LEFT OUTER JOIN TABLEC C ON B.Y = C.Y
WHERE A.MY_COL = @col_val
you should use inner join (or join) for TABLEL
您应该对 TABLEL 使用内部连接（或连接）
回答by Joe Taras
Try this:
尝试这个：
SELECT *
FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.X = B.X
LEFT OUTER JOIN TABLEC C ON B.Y = C.Y
INNER JOIN TABLED D ON D.Z = A.Z
WHERE MY_COL = @col_val
TABLEBand TABLECgoes in LEFT OUTER JOIN(you have used +), instead TABLEDgoes in INNER JOIN
TABLEB然后TABLEC进入LEFT OUTER JOIN（你用过+），而是TABLED进入INNER JOIN
