内部加入和外连接表的顺序是重要的吗？ [英] Inner join & outer join; is the order of tables in from important?

问题描述

为什么在组合外部&时表的顺序很重要内部联接？
以下内容在postgres中失败：

Why is the order of tables important when combining an outer & an inner join ? the following fails with postgres:

SELECT grp.number AS number,     
       tags.value AS tag   
FROM groups grp,
     insrel archiverel  
LEFT OUTER JOIN ownrel ownrel ON grp.number = ownrel.dnumber   
LEFT OUTER JOIN tags tags ON tags.number = ownrel.snumber   
WHERE archiverel.snumber = 11128188 AND    
      archiverel.dnumber = grp.number 

结果：

ERROR:  invalid reference to FROM-clause entry for table "grp" LINE 5: LEFT OUTER JOIN ownrel ownrel ON grp.number = ownrel.d... 
^ HINT:  There is an entry for table "grp", but it cannot be referenced from this part of the query.

当在FROM中反转组时，一切正常：

when the groups are reversed in the FROM it all works:

SELECT  grp.number AS number,     
        tags.value AS tag   
FROM    insrel archiverel,
        groups grp
LEFT OUTER JOIN ownrel ownrel ON grp.number = ownrel.dnumber   
LEFT OUTER JOIN tags tags ON tags.number = ownrel.snumber   
WHERE   archiverel.snumber = 11128188 AND    
        archiverel.dnumber = grp.number 

推荐答案

我相信您可以将其视为

写此代码时：

FROM groups grp,
     insrel archiverel  
LEFT OUTER JOIN ownrel ownrel ON grp.number = ownrel.dnumber   
LEFT OUTER JOIN tags tags ON tags.number = ownrel.snumber   

我认为解析器是这样解释的：

I think it is interpreted by the parser like this:

FROM groups grp,
(
  (
     insrel archiverel  
     LEFT OUTER JOIN ownrel ownrel ON grp.number = ownrel.dnumber   
  )
LEFT OUTER JOIN tags tags ON tags.number = ownrel.snumber
)

如果是这样，则在最里面的连接中没有绑定 grp。

If so, then in the innermost join "grp" is unbound.

当用 groups和 insrel反转行时，最里面的联接适用于 groups和 ownrel，因此它起作用。

When you reverse the lines with "groups" and "insrel", the innermost join applies to "groups" and "ownrel", so it works.

    FROM groups grp
         JOIN insrel archiverel  ON archiverel.dnumber = grp.number
    LEFT OUTER JOIN ownrel ownrel ON grp.number = ownrel.dnumber   
    LEFT OUTER JOIN tags tags ON tags.number = ownrel.snumber 
WHERE archiverel.snumber = 11128188

这篇关于内部加入和外连接表的顺序是重要的吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！