SQL 左外连接（三个表之间的连接）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/294543/
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
Left outer join (join between three tables)?
提问by Berlin Brown
I have a main table that I must get data from. I have a left outer join where the fields will match 40% of the time. And then I have another join where I need to match the data from table A with.
我有一个必须从中获取数据的主表。我有一个左外连接，其中字段匹配 40% 的时间。然后我有另一个连接，我需要将表 A 中的数据与之匹配。
This is the SQL in pseudo code. This query won't work.
这是伪代码中的 SQL。此查询将不起作用。
-- This is the part I want to do but doesn't work. AND H.COL3 = A.STATE????
-- 这是我想做但不起作用的部分。和 H.COL3 = A.STATE??
I am working with IBM DB2.
我正在使用 IBM DB2。
SELECT DISTINCT
APP_NO as app_no,
A.STATE as state
...
... Fields
...
FROM
TABLE_A A
LEFT OUTER JOIN
TABLE_B HIST
ON
HIST.COL1 = A.COL1
, TABLE_C B
LEFT OUTER JOIN
TABLE_D H
ON
H.COL2 = B.COL2
-- This is the part I want to do but doesn't work.
AND
H.COL3 = A.STATE????
WHERE
A.BRANCH = 'Data'
回答by Mark
I think you could re-write it like this (but I could be reading your statement wrong)
我想你可以像这样重写它（但我可能读错了你的陈述）
FROM
TABLE_A A LEFT OUTER JOIN TABLE_B HIST ON
HIST.COL1 = A.COL1
LEFT OUTER JOIN TABLE_D H ON
H.COL3 = A.STATE
LEFT OUTER JOIN TABLE_C B ON H.COL2 = B.COL2
WHERE
A.BRANCH = 'Data'
Also, the IBM doco on this error states:
此外，关于此错误的 IBM doco 指出：
An ON clause associated with a JOIN operator or in a MERGE statement is not valid. Explanation:
与 JOIN 运算符关联或在 MERGE 语句中的 ON 子句无效。解释：
Column references in an ON clause must only reference columns of tables that are in the scope of the ON clause.
ON 子句中的列引用只能引用在 ON 子句范围内的表的列。
So I could be mistaken, it just looks like the erronous ON clause when outer joining "H.COL3 = A.STATE" is out of scope of the On clause because the table A is not in that scope.
所以我可能弄错了，当外部连接“H.COL3 = A.STATE”超出 On 子句的范围时，它看起来像是错误的 ON 子句，因为表 A 不在该范围内。
回答by Murat Ayfer
What happens if you put the "AND H.COL3 = A.STATE" in your WHERE clause?
如果将“AND H.COL3 = A.STATE”放在 WHERE 子句中会发生什么？
