Re: NATURAL JOINs

Scott Marlowe wrote:
> On Wed, Oct 15, 2008 at 10:17 AM, Richard Broersma
> <richard(dot)broersma(at)gmail(dot)com> wrote:
>> On Wed, Oct 15, 2008 at 9:03 AM, Reg Me Please <regmeplease(at)gmail(dot)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.