Обсуждение: join ... using ... and - is this expected behaviour?

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? The following is...

select column from table join table on (column = column) and column = clause

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)

The following test case illustrates the issue:

CREATE TABLE table1
(
  columnone integer,
  columntwo integer
);

CREATE TABLE table2
(
  columntwo integer,
  columnthree integer
);

insert into table1 values (1, 1), (2, 1);

insert into table2 values (1, 3);

This query results in a syntax error:

select t1.columnone, t1.columntwo, t2.columnthree
from table1 t1 join table2 t2
using (columntwo) and columnone = 1

This query executes as expected:

select t1.columnone, t1.columntwo, t2.columnthree
from table1 t1 join table2 t2
on (t1.columntwo = t2.columntwo) and columnone = 1

Rob Johnston

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.