Re: join ... using ... and - is this expected behaviour?

On Tue, 3 Jun 2008, Rob Johnston wrote:

> Just wondering if this is expected behaviour. When executing a query in
> the form of:
>
> select column from table join table using (column) and column = clause
>
> pgsql (8.2) returns the following: syntax error at or near "and"
>
> Obviously, you can get around this by using "where" instead of "and",
> but shouldn't the format as used above be valid?

No.

> The following is...
>
> select column from table join table on (column = column) and column = clause

Yes.

USING takes a column list, ON takes an expression as a search condition
(and note from the syntax section that the parens are not always required
around the expression). "(t1.col1 = t2.col1) AND col3 = foo" is still a
valid search condition, but "(col1) AND col3 = foo" isn't a valid column
list.

> The documentation indicates that the two formats of the query are
> equivalent
> (http://www.postgresql.org/docs/8.2/interactive/queries-table-expressions.html#QUERIES-JOIN)

It's talking about the execution, not the syntax, but that could probably
be more clear.