Thread: NATURAL JOINs

Hi all.

I'm running v8.3.3

First point.
Is there a way to know how a NATURAL JOIN is actually done?
That is, which fields are actually used for the join?
The EXPLAIN directive doesn't show anyting useful.

Second point.
I have this:

CREATE TABLE tab_dictionary ( item text primary key );

CREATE TABLE tab_atable(
  item1 TEXT NOT NULL REFERENCES tab_dictionary( item ),
  item2 TEXT NOT NULL REFERENCES tab_dictionary( item ),
  trans NUMERIC NOT NULL
);

INSERT INTO tab_dictionary VALUES ( 'meters' ),('feet' );

INSERT INTO tab_atable VALUES ( 'meters','feet',3.28084 );

SELECT * FROM tab_atable NATURAL JOIN tab_dictionary;
 item1  | item2 |  trans  |  item
--------+-------+---------+--------
 meters | feet  | 3.28084 | meters
 meters | feet  | 3.28084 | feet
(2 rows)

Very likely I'm wrong, but this output looks wrong to me (and shold be wrong
also accordingly to the documentation).
Is there ant good explaination to this behaviour?

Thanks.

On Mon, Oct 13, 2008 at 9:52 AM, Reg Me Please <regmeplease@gmail.com> wrote:

> Is there a way to know how a NATURAL JOIN is actually done?

Here is what the manual says about natural joins:
http://www.postgresql.org/docs/8.3/interactive/queries-table-expressions.html#QUERIES-FROM

...
Finally, NATURAL is a shorthand form of USING: it forms a USING list
consisting of exactly those column names that appear in both input
tables. As with USING, these columns appear only once in the output
table.
...

...
USING is a shorthand notation: it takes a comma-separated list of
column names, which the joined tables must have in common, and forms a
join condition specifying equality of each of these pairs of columns.
Furthermore, the output of a JOIN USING has one column for each of the
equated pairs of input columns, followed by all of the other columns
from each table. Thus, USING (a, b, c) is equivalent to ON (t1.a =
t2.a AND t1.b = t2.b AND t1.c = t2.c) with the exception that if ON is
used there will be two columns a, b, and c in the result, whereas with
USING there will be only one of each.
...

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

"Richard Broersma" <richard.broersma@gmail.com> writes:
> On Mon, Oct 13, 2008 at 9:52 AM, Reg Me Please <regmeplease@gmail.com> wrote:
>> Is there a way to know how a NATURAL JOIN is actually done?

> Here is what the manual says about natural joins:
> http://www.postgresql.org/docs/8.3/interactive/queries-table-expressions.html#QUERIES-FROM

> Finally, NATURAL is a shorthand form of USING: it forms a USING list
> consisting of exactly those column names that appear in both input
> tables. As with USING, these columns appear only once in the output
> table.

The OP's case is actually giving a cartesian product, because the tables
don't have any column names in common.

You'd think this should be an error, but AFAICS the SQL spec requires it
to behave that way.

            regards, tom lane

On Tue, Oct 14, 2008 at 11:17 PM, regme please <regmeplease@gmail.com> wrote:

> Well, it could make some sense to extend the semantics when you have
> explicit "REFERENCES" to tables in the JOINs.
> Or at least warn or notice the user that the "NATURAL (INNER) JOIN" has
> actuallt been converted into a CROSS one.
> It would not be standard but helpful for developers.

For this reason, clients passing natural joins to the server can have
dangerous result sets returned with no warning.  If one of the joined
columns were to have its name altered, then clients issuing these
queries wouldn't imediately know that there was a problem.  On the
otherhand, a client application joins using the ON clause will get an
error when PostgreSQL notices the nonexistant column reference.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

"Richard Broersma" <richard.broersma@gmail.com> writes:
> For this reason, clients passing natural joins to the server can have
> dangerous result sets returned with no warning.

Yeah.  A lot of people consider that NATURAL JOIN is simply a bad idea
and shouldn't be used ever --- it's too easy to shoot yourself in the
foot with a careless column addition or rename.  Explicitly spelling out
the join columns with ON or USING is a lot less prone to silent breakage
after a schema change.

            regards, tom lane

Il Wednesday 15 October 2008 17:55:03 Tom Lane ha scritto:
> "Richard Broersma" <richard.broersma@gmail.com> writes:
> > For this reason, clients passing natural joins to the server can have
> > dangerous result sets returned with no warning.
>
> Yeah.  A lot of people consider that NATURAL JOIN is simply a bad idea
> and shouldn't be used ever --- it's too easy to shoot yourself in the
> foot with a careless column addition or rename.  Explicitly spelling out
> the join columns with ON or USING is a lot less prone to silent breakage
> after a schema change.
>
>             regards, tom lane

Both are perfectly right, indeed.
Nonetheless, in my opinion a NATURAL JOIN  exploiting the FKs
instead of the column names would be much more helpful and much less error
prone!

As far as I know there is no way to exploit FKs in JOINs, right?

THANKS

On Wed, Oct 15, 2008 at 9:03 AM, Reg Me Please <regmeplease@gmail.com> wrote:

> Both are perfectly right, indeed.
> Nonetheless, in my opinion a NATURAL JOIN  exploiting the FKs
> instead of the column names would be much more helpful and much less error
> prone!
>
> As far as I know there is no way to exploit FKs in JOINs, right?

Yes AFAIK, this would make postgresql's implementation of natural join
violate the SQL standard.  Perhaps you could propose an "UNNATURAL
JOIN" syntax extension. ;)


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Wed, Oct 15, 2008 at 10:17 AM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> On Wed, Oct 15, 2008 at 9:03 AM, Reg Me Please <regmeplease@gmail.com> wrote:
>
>> Both are perfectly right, indeed.
>> Nonetheless, in my opinion a NATURAL JOIN  exploiting the FKs
>> instead of the column names would be much more helpful and much less error
>> prone!
>>
>> As far as I know there is no way to exploit FKs in JOINs, right?
>
> Yes AFAIK, this would make postgresql's implementation of natural join
> violate the SQL standard.  Perhaps you could propose an "UNNATURAL
> JOIN" syntax extension. ;)

Or a "VERY VERY NATURAL JOIN" syntax?  :)

Scott Marlowe wrote:
> On Wed, Oct 15, 2008 at 10:17 AM, Richard Broersma
> <richard.broersma@gmail.com> wrote:
>> On Wed, Oct 15, 2008 at 9:03 AM, Reg Me Please <regmeplease@gmail.com> wrote:
>>
>>> Both are perfectly right, indeed.
>>> Nonetheless, in my opinion a NATURAL JOIN  exploiting the FKs
>>> instead of the column names would be much more helpful and much less error
>>> prone!
>>>
>>> As far as I know there is no way to exploit FKs in JOINs, right?
>> Yes AFAIK, this would make postgresql's implementation of natural join
>> violate the SQL standard.  Perhaps you could propose an "UNNATURAL
>> JOIN" syntax extension. ;)
>
> Or a "VERY VERY NATURAL JOIN" syntax?  :)
>

Yeah, while we're at it, why don't we ask Tom to implement the
DO_THE_QUERY_I_WANT_BUT_CANT_EXPRESS_IN_SQL syntax? That would
solve a whole class of problems the newbie DBAs face every day. :)

Seriously, I find NATURAL joins useful. While I understand the
"design queries resilient to schema changes" ("explicit is better
than implicit" in the Zen of Python), I think if you design your
schema so that you can use NATURAL joins, you've done good (I like
using the same identifier for the same object, expecially pkeys,
when used in different places, e.g. as foreign keys in another
table, and conversely never use the same name for different
objects). Breaking NATURAL joins acts like a safeguard, when you've
done something bad to your schema.

Let's not forget SQL serves for two purposes... for embedding queries
in applications (then, yes, those queries should be as resilient as
possible) or for querying a db interactively. That's why it has been
designed with a syntax resembling natural language. When used
interactively, natural joins rock. Less typing and less thinking
about column names.

As for the OP request, my answer is "just give KFs the same name of
the PKs they refer to AND never use the same name for columns in
different tables if they're not the same thing", which seems both...
hmm, natural, to me.

.TM.