Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Jodi Kanter <jkanter@virginia.edu>,
	Postgres SQL List <pgsql-sql@postgresql.org>
Subject: Re: join syntax
Date: Tue, 17 Jun 2003 11:48:09 -0700
User-Agent: KMail/1.4.3
References: <3EEF6170.1040905@virginia.edu>
In-Reply-To: <3EEF6170.1040905@virginia.edu>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200306171148.09162.josh@agliodbs.com>

Jodi,

> One of my colleagues has created a database where he has the same field=
=20
> name in two tables and uses this field to link his tables rather than=20
> some arbitrary value. For example, he has used "exp_id" in two tables.=20
> When writing his joins he uses a syntax that says something like JOIN ON=
=20
> EXP_ID. Can someone tell me what that syntax should be? I am not very=20
> familiar with it since I typically use the syntax where one field is set=
=20
> equal to the other.

table_a JOIN table_b USING (exp_id)

> Personally I prefer not to set databases up this way but cannot seem to=
=20
> convince him of this. And yet I am supposed to now help him with his=20
> database and application..=20

That, we can't help you with.

Point out to your friend, though, that an explicit JOIN order can often har=
m=20
performance when the JOIN order given by the query writer is not ideal.  Th=
at=20
is, the "FROM table, table, table WHERE expression, expression" syntax give=
s=20
the parser a freer hand to choose the fastest execution method.  Of course,=
=20
on a very small database that typically makes litte difference.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco