Re: Cartesian product bug? - Mailing list pgsql-general

From Bruce Momjian
Subject Re: Cartesian product bug?
Date
Msg-id 200311070402.hA7421j08533@candle.pha.pa.us
Whole thread Raw
In response to Re: Cartesian product bug?  (Greg Stark <gsstark@mit.edu>)
List pgsql-general
Greg Stark wrote:
>
> "scott.marlowe" <scott.marlowe@ihs.com> writes:
>
> > On Fri, 31 Oct 2003, Tom Lane wrote:
> >
> > > (Personally I think NATURAL JOIN is an evil, bug-prone construct,
> > > precisely because coincidental matches of column names will mess up your
> > > results.)
> >
> > Me too.  When I first saw it, I figured it would "naturally join" the two
> > tables on their fk/pk relation if there was one.  That seems natural.
> > Joining on two fields that just happen to have the same name is unnatural
> > to me.
>
> Well 99% of the time I impose on myself a constraint to only use the same name
> iff they refer to the same attribute. So if they have the same name then they
> really ought to be a reasonable join clause.
>
> However the 1% are things like "date_created, date_updated" or even flags like
> "active", "deleted" etc. Which are more than enough to make it utterly
> useless.
>
> Too bad really, it would be a handy thing for ad-hoc queries typed at psql. It
> would still seem too fragile for production queries though.

I think the reason they don't use pk/fk in natural joins is because you can
join all sorts of results, like SELECT in FROM, that doesn't always have
a meaningful pk/fk.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

pgsql-general by date:

Previous
From: sgupta5@ncsu.edu
Date:
Subject:
Next
From:
Date:
Subject: Re: on connect trigger?