Syntax confusion by using the 'on'-clause

[Roland]

Hi folks,

suppose, there are 2 tables, each of them with a key called 'id'.
At first, i tried this:

select t1.id, t2.col from table1 as t1 , table2 as t2 where t1.id=t2.id

- and it worked fine.

To increase the performance (in fact, i will have more than 1 million rows!),
I read several docs and tried a equi-join with on-clause:

select t1.id, t2.col from table1 as t1 JOIN table2 as t2 ON t1.id=t2.id

- and this is what i get:
MySQL said: You have an error in your SQL syntax near 'ON t1.id=t2.id' at line 1

I ransacked my docs again but I don't get on it, what's wrong!

Any help would be greatly appreciated!

 

[qmi]

I struck the same problem. According to the documentation, the ON clause can only be used with a LEFT (outer) JOIN.

From the MySQL documentation...
-----------------------
MySQL supports the following JOIN syntaxes for use in SELECT statements:

table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference ON conditional_expr
table_reference LEFT [OUTER] JOIN table_reference USING (column_list)
table_reference NATURAL LEFT [OUTER] JOIN table_reference
{ oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
------------

I've tried using ON with a LEFT JOIN and it works, but not with an inner join - you must specify how to join the tables in the WHERE clause. This shouldn't be any slower than using ON since the server processes the WHERE clause first, to filter out all the unwanted rows, before joining tables together.

 

[Roland]

Yo!
Dead right, you got it. Thanks a lot!