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
An INNER JOIN is such type of join that returns all rows from both the participating tables where the key record of one table is equal to the key records of another table. This type of join required a comparison operator to match rows from the participating tables based on a common field or column of both the tables.
The INNER JOIN selects the common rows between two tables. Whereas the RIGHT JOIN selects the common rows as well as all the remaining rows from the right table. The INNER JOIN selects the common rows between two tables. Whereas the FULL OUTER JOIN selects all the rows from both the tables.
Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!
Inner joins combine records from two tables whenever there are matching values in a field common to both tables. You can use INNER JOIN with the Departments and Employees tables to select all the employees in each department.
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
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!)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With