postgresql 内连接和外连接；表的顺序从重要吗？

Inner join & outer join; is the order of tables in from important?

提问by Thies Edeling

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

为什么在组合外连接和内连接时表的顺序很重要？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 

with result:

结果：

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.

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

当组在 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 

回答by Dave Costa

I believe that you can think of this as an operator precedence issue.

我相信您可以将其视为运算符优先级问题。

When you write 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   

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
)

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

如果是这样，那么在最里面的连接中“grp”是未绑定的。

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

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

Probably this would work as well:

可能这也会起作用：

    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

回答by Cowan

I don't think anyone's quite nailed this, or explained it very well. You're combining 'old style' (theta) and 'new style' (ANSI) joins, which I strongly suspect are being grouped in ways you don't expect. Look at it this way:

我认为没有人完全掌握这一点，或者很好地解释了这一点。您正在组合“旧样式”（theta）和“新样式”（ANSI）连接，我强烈怀疑它们以您意想不到的方式分组。这样看：

SELECT * FROM a, b JOIN c ON a.x = c.x

is like saying

就像在说

SELECT * FROM a, (b JOIN c on a.x = c.x)

where the bracketed thing represents a bunch of tables merged into one virtual table, to be joined on with a theta-join against 'a'. Obviously the 'a' table can't be part of the join as it's only being joined onto later. Reverse it, and you're doing

其中括号内的东西表示合并到一个虚拟表中的一堆表，以针对“a”的 theta-join 进行连接。显然，'a' 表不能成为连接的一部分，因为它只是稍后被连接。反转它，你正在做

SELECT * FROM b, (a JOIN c on a.x = c.x)

which is perfectly understandable and so fine. I'm not sure why you're not using ANSI join syntax for all of it though, seems a little weird (and cruel to the person who has to maintain it!)

这是完全可以理解的，而且很好。我不确定你为什么不使用 ANSI 连接语法，但似乎有点奇怪（对必须维护它的人来说很残忍！）

回答by RB.

Because in the first one grp is not part of the join the ON clause belongs to.

因为在第一个 grp 不是 ON 子句所属的连接的一部分。

回答by Jeffrey L Whitledge

I don't know what is causing that behavior, if it's a bug or by design, but it should work fine if you stick with one form of join or the other.

我不知道是什么导致了这种行为，是错误还是设计使然，但是如果您坚持使用一种形式的连接或另一种形式，它应该可以正常工作。

SELECT grp.number AS number,     
       tags.value AS tag   
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

I would be interested to know more if the behavior is by design.

如果行为是设计使然，我很想知道更多。

回答by Joel Coehoorn

For an inner join, the order of the tables is not important.

对于内部联接，表的顺序并不重要。

For an outer join, it is. Allthe rows from the table on the side specified (is it a LEFT or RIGHT join) will be included, while only rows that match the join criteria will be included from the table on the other side.

对于外连接，它是。 将包括指定一侧表中的所有行（是左联接还是右联接），而只有符合联接条件的行才会被包括在另一侧的表中。

Because OUTER JOINS keep all rows from one side, they are said to (in general) increase result sets. INNER JOINS only keep rows from both sides if they match, so they are said (in general) to reduce result sets. Thus, you typically want to do your INNER JOINS before the OUTER JOINS (when possible).

因为 OUTER JOINS 从一侧保留所有行，所以据说它们（通常）增加了结果集。INNER JOINS 仅在匹配时保留两侧的行，因此它们被称为（通常）以减少结果集。因此，您通常希望在 OUTER JOINS 之前执行 INNER JOINS（如果可能）。

In your case, it's almost certainly a result of the evil A,B syntax.

在您的情况下，这几乎可以肯定是邪恶的 A,B 语法的结果。