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

Question

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

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:

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

Answer 1

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

Answer 2

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.

Answer 3

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

For an outer join, it is. All the 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).

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

Answer 4

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:

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

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!)

Answer 5

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.

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

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