Thread: Multiple natural joins

Hi,

I've read the PostgreSQL documentation on natural joins, but it only ever shows it being used once.  Is it possible to use it more than once?

Say if I have the following 3 tables (this is a poor example by the way):

tbl_a
--------
a_id serial NOT NULL
location text

tbl_b
--------
b_id serial NOT NULL
language text

tbl_c
--------
c_id serial NOT NULL
a_id int NOT NULL
b_id int NOT NULL

I could write:

SELECT tbl_a.location, tbl_b.language
FROM tbl_c
INNER JOIN tbl_a ON tbl_c.a_id = tbl_a.a_id
INNER JOIN tbl_b ON tbl_c.b_id = tbl_b.b_id;

But could I also write:

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?

Thanks

Thom

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.