Multiple natural joins

On Thu, Mar 19, 2009 at 5:24 AM, Thom Brown <thombrown@gmail.com> wrote:

The confusion comes when 2 of those tables reference the 3rd table using the
same column.

So are natural joins only allowed to join 2 tables?  If not, how can it be
used for more than 1 table has links to the other tables?

Not sure how well natural joins work on > 2 tables, but they are to be
avoided in my opinion.

Thom Brown wrote:

SELECT tbl_a.location, tbl_b.language
FROM tbl_c
NATURAL INNER JOIN tbl_a
NATURAL INNER JOIN tbl_b

The confusion comes when 2 of those tables reference the 3rd table using
the same column.

So are natural joins only allowed to join 2 tables? If not, how can it
be used for more than 1 table has links to the other tables?

The fact that the above example runs proves that natural joins are
allowed with more than 2 tables.

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

and that means that the second join is executed as if the first join
resulted in a normal table with normal columns with names as the case
may be, and the natural join will use those names with all the consequences.

If you want a different order, you can set the parentheses differently,
with possibly different results.

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

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/

Sam Mason wrote:

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.

Right.