Richard Hipp wrote: > On 1/5/20, Keith Medcalf <kmedc...@dessus.com> wrote: >> select * from a, b, c using (id); -- very strange result > > PostgreSQL and MySQL process the query as follows: > > SELECT * FROM a, (b JOIN c USING(id)); > > SQLite processes the query like this: > > SELECT * FROM (a,b) JOIN c USING (id); > > I don't know which is correct. Perhaps the result is undefined.
Assuming the following query: SELECT * FROM a, b JOIN c USING (id); SQL-92 says: | 7.4 <from clause> | | <from clause> ::= FROM <table reference> [ { <comma> <table reference> }... ] | | 6.3 <table reference> | | <table reference> ::= | <table name> [ [ AS ] <correlation name> | [ <left paren> <derived column list> <right paren> ] ] | | <derived table> [ AS ] <correlation name> | [ <left paren> <derived column list> <right paren> ] | | <joined table> | | 7.5 <joined table> | | <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> ] It is not possible to have such a <comma> inside a <table reference>, so b and c must be joined first. SQLite actually parses the comma as a join: SELECT * FROM a CROSS JOIN b JOIN c USING (id); If the query were written like this, joining a and b first would be correct. (As far as I can see, the standard does not say how to handle ambiguous parts of the grammar, so it would also be allowed to produce "b JOIN c" first.) Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users