Обсуждение: 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
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.