SQL 一个查询中多个表的多个左连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14260860/
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 joins on multiple tables in one query
提问by DeniseMeander
I've got one mastertable, which has items stored in multiple levels, parents and childs, and there is a second table which may or may not have additional data. I need to query two levels from my master table and have a left join on my second table, but because of the ordering within my query this will not work.
我有一个master表，其中包含存储在多个级别（父级和子级）中的项目，还有第二个表可能有也可能没有附加数据。我需要从我的主表中查询两个级别，并在我的第二个表上有一个左连接，但由于我的查询中的排序，这将不起作用。
SELECT something FROM master as parent, master as child
LEFT JOIN second as parentdata ON parent.secondary_id = parentdata.id
LEFT JOIN second as childdata ON child.secondary_id = childdata.id
WHERE parent.id = child.parent_id AND parent.parent_id = 'rootID'
The left join only works with the last table in the from clause, so I am only able to make it work for one of the left joins. In the example above none of the left joins will work because the first left join points towards the first table in the from clause, the second one will never work like this.
左联接仅适用于 from 子句中的最后一个表，因此我只能使其适用于左联接之一。在上面的例子中，没有一个左连接可以工作，因为第一个左连接指向 from 子句中的第一个表，第二个永远不会像这样工作。
How can I make this work?
我怎样才能使这项工作？
回答by Erwin Brandstetter
This kind of query should work - after rewriting with explicit JOINsyntax:
这种查询应该可以工作 - 在使用显式JOIN语法重写后：
SELECT something
FROM master parent
JOIN master child ON child.parent_id = parent.id
LEFT JOIN second parentdata ON parentdata.id = parent.secondary_id
LEFT JOIN second childdata ON childdata.id = child.secondary_id
WHERE parent.parent_id = 'rootID'
The tripping wire here is that an explicit JOINbinds before "old style" CROSS JOINwith comma (,). I quote the manual here:
这里的绊线是JOIN在“旧样式”之前CROSS JOIN用逗号 ( ,)显式绑定。我在这里引用手册：
In any case
JOINbinds more tightly than the commas separatingFROM-list items.
在任何情况下，
JOIN绑定都比逗号分隔FROM-list 项目更紧密 。
After rewriting the first, all joins are applied left-to-right (logically - Postgres is free to rearrange tables in the query plan otherwise) and it works.
在重写第一个之后，所有的连接都是从左到右应用的（逻辑上 - Postgres 可以自由地重新排列查询计划中的表）并且它可以工作。
Just to make my point, this would work, too:
只是为了说明我的观点，这也可以：
SELECT something
FROM master parent
LEFT JOIN second parentdata ON parentdata.id = parent.secondary_id
, master child
LEFT JOIN second childdata ON childdata.id = child.secondary_id
WHERE child.parent_id = parent.id
AND parent.parent_id = 'rootID'
But explicit JOINsyntax is generally preferable, as your case illustrates once again.
但是显式JOIN语法通常更可取，正如您的情况再次说明的那样。
And be aware that multiple (LEFT) JOINcan multiply rows:
请注意，多个 ( LEFT)JOIN可以将行相乘：
回答by pradipgarala
You can do like this
你可以这样做
SELECT something
FROM
(a LEFT JOIN b ON a.a_id = b.b_id) LEFT JOIN c on a.a_aid = c.c_id
WHERE a.parent_id = 'rootID'
回答by Daniel Sparing
The JOINstatements are also part of the FROMclause, more formally a join_typeis used to combine two from_item's into one from_item, multiple one of which can then form a comma-separated list after the FROM. See http://www.postgresql.org/docs/9.1/static/sql-select.html.
这些JOIN语句也是FROM子句的一部分，更正式地说，join_type用于将两个from_item组合成一个from_item，其中多个可以在 之后形成逗号分隔的列表FROM。请参阅http://www.postgresql.org/docs/9.1/static/sql-select.html。
So the direct solution to your problem is:
所以你的问题的直接解决方案是：
SELECT something
FROM
master as parent LEFT JOIN second as parentdata
ON parent.secondary_id = parentdata.id,
master as child LEFT JOIN second as childdata
ON child.secondary_id = childdata.id
WHERE parent.id = child.parent_id AND parent.parent_id = 'rootID'
A better option would be to only use JOIN's, as it has already been suggested.
更好的选择是只使用JOIN's，正如已经建议的那样。
