Postgres Pro
Downloads
Home   >   mailing lists

Re: NATURAL JOINs - Mailing list pgsql-general

From regme please
Subject Re: NATURAL JOINs
Date
Msg-id 446c7e870810142317s140eb28cybe0127ae82039511@mail.gmail.com
Whole thread Raw
In response to Re: NATURAL JOINs  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: NATURAL JOINs  ("Richard Broersma" <richard.broersma@gmail.com>)
List pgsql-general
Tree view
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.

Thanks.

2008/10/13 Tom Lane <tgl@sss.pgh.pa.us>
"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

pgsql-general by date:

Previous
From: gorsa
Date:
Subject: Re: how to get unique identifier for a client
Next
From: Greg Smith
Date:
Subject: Re: Drupal and PostgreSQL - performance issues?