在一个查询中对多个表进行多个左联接 [英] Multiple left joins on multiple tables in one query

问题描述

我有一个master表，该表具有存储在多个级别(父级和子级)中的项，还有第二个表，该表可能具有也可能没有其他数据.我需要从主表中查询两个级别，并在第二个表上进行左联接，但是由于查询中的顺序，这将无法正常工作.

I've got one master table, 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.

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'

左联接仅与from子句中的最后一个表一起使用，因此我只能使其对左联接之一起作用.在上面的示例中，左联接都不起作用，因为第一个左联接指向from子句中的第一个表，而第二个联接则永远不会这样.

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.

我该如何进行这项工作?

How can I make this work?

推荐答案

这种查询应该可以工作-用显式ANSI JOIN语法重写后:

This kind of query should work - after rewriting with explicit ANSI JOIN syntax:

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'

这里的绊脚石是明确的JOIN在旧样式" CROSS JOIN之前用逗号( , )进行绑定. 我在此处引用该手册:

The tripping wire here is that an explicit JOIN binds before "old style" CROSS JOIN with comma (,). I quote the manual here:

无论如何，JOIN的绑定比逗号分隔更紧密 FROM-列表项.

In any case JOIN binds more tightly than the commas separating FROM-list items.

在重写第一个之后，所有联接都从左到右应用(从逻辑上讲-Postgres可以自由地在查询计划中重新排列表)，并且可以正常工作.

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.

我只想指出一点，这也行得通:

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'

但是，如您的情况再次说明的那样，显式JOIN语法通常更可取.

But explicit JOIN syntax is generally preferable, as your case illustrates once again.

请注意，多个(LEFT)JOIN可以使行相乘:

And be aware that multiple (LEFT) JOINs can multiply rows:

• 两个SQL LEFT JOINS产生不正确的结果

这篇关于在一个查询中对多个表进行多个左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！