Re: BUG #5506: Error in the grammar of de joins

"Fernando Cano" <fcano(at)uniovi(dot)es> writes:
> This sentences are valid with your grammar but generate an error.

> create table t1 ( id_t1 smallint, name text);
> create table t2 ( id_t2 smallint, name text);

> select * from natural join using (id) ;
> select * from t1 natural cross join t2;
> select * from natural cross join using (id) ;
> select * from t1 join t2;
> select t1 natural left join t2;

Uh, no, they're *not* valid with our grammar --- that's why you're
getting errors. They're not valid according to the SQL standard
either, so I'm not sure exactly what your point is.

> I have problems when I explain to my students the sintax of the joins.

The SQL92 standard defines join syntax like this:

<joined table> ::=
<cross join>
| <qualified join>
| <left paren> <joined table> <right paren>

<cross join> ::=
<table reference> CROSS JOIN <table reference>

<qualified join> ::=
<table reference> [ NATURAL ] [ <join type> ] JOIN
<table reference> [ <join specification> ]

<join specification> ::=
<join condition>
| <named columns join>

<join condition> ::= ON <search condition>

<named columns join> ::=
USING <left paren> <join column list> <right paren>

<join type> ::=
INNER
| <outer join type> [ OUTER ]
| UNION

<outer join type> ::=
LEFT
| RIGHT
| FULL

<join column list> ::= <column name list>

The restriction that NATURAL can't appear with ON or USING isn't
in this syntax diagram, though; it's explained in the text.

regards, tom lane