=?utf-8?Q?=C3=85smund_Kveim_Lie?= <asmundkl@skipthis.ifi.uio.no> writes:
> SELECT * FROM a,b NATURAL JOIN c;
> SELECT * FROM a CROSS JOIN b NATURAL JOIN c;
> These two example queries should give the same result.
No, they shouldn't, because JOIN binds more tightly than comma. The
first is equivalent to
SELECT * FROM a CROSS JOIN (b NATURAL JOIN c);
while in the second case the JOINs associate left-to-right, giving
SELECT * FROM (a CROSS JOIN b) NATURAL JOIN c;
Because you have columns with the same names in A and C, the second
NATURAL JOIN has a different implicit join clause than the first.
(Personally I think NATURAL JOIN is an evil, bug-prone construct,
precisely because coincidental matches of column names will mess up your
results.)
> In the first query, it seems like it’s doing the natural
> join between b and c, and then does the Cartesian product on that
> result with a. On the second query, it does as we assume it should,
> namely does the Cartesian product first.
I think your expectations have been set by MySQL, which last I heard
interprets all joins as being done left-to-right. That's not compliant
with the SQL standard, however.
regards, tom lane