Re: Multiple natural joins

On Thu, Mar 19, 2009 at 04:22:56PM +0200, Peter Eisentraut wrote:
> Joins nest from left to write, so
>
> tbl_c NATURAL JOIN tbl_a NATURAL JOIN tbl_b
>
> means
>
> (tbl_c NATURAL JOIN tbl_a) NATURAL JOIN tbl_b
>
> If you want a different order, you can set the parentheses differently,
> with possibly different results.

When would you get different results? As far as I can tell, for INNER
joins, you'd always get the same thing back for any ordering. With
OUTER joins it obviously matters what's going on, but that's expected.

The simplest case would appear to be three tables:

CREATE TABLE a ( a INTEGER );
CREATE TABLE b ( b INTEGER );
CREATE TABLE ab ( a INTEGER, b INTEGER );

It always seems possible to rewrite a set of NATURAL joins as a
conventional cross join; i.e. from:

SELECT a.a, b.b
FROM a NATURAL JOIN b NATURAL JOIN ab

into:

SELECT a.a, b.b
FROM a, b, ab
WHERE a.a = ab.a
AND b.b = ab.b;

The order you happen to write the tables in either style doesn't seem
to matter. What's nice with the NATURAL join is that the database
knows that "a" is unambiguous and doesn't complain as it would as it
would if I'd put an unqualified "a" in the second query. This is just
syntactic-sugar and doesn't alter the semantics, at least not that I can
tell.

> The fact that this isn't entirely obvious only supports the argument
> that natural joins shouldn't used.

It may not be obvious what's going on and I'd always recommend to
never use NATURAL joins but I think their semantics are quite easy
to understand.

They do require a *lot* of care when using them as it's easy to change
the semantics of existing queries by adding an inappropriately named
column to a table. The reason I don't use NATURAL joins is because of
these non-local side effects (i.e. changing a table can cause seemingly
unrelated queries to suddenly stop working). It's a similar reason why
GOTOs and global variables are frowned upon--it's just too easy to break
code accidentally.

--
Sam http://samason.me.uk/