postgresql – 内连接和外连接;表中的顺序是重要吗？

为什么组合顺序是重要的？内连接？
以下失败与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中相反时，它们都可以工作：

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

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

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”是没有绑定的。

当您使用“groups”和“insrel”反转行时，最内层的连接适用于“groups”和“ownrel”，因此它可以正常工作。

这可能也会奏效：

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

postgresql – 内连接和外连接;表中的顺序是重要吗？

猜你在找的Postgre SQL相关文章