How to use mysql JOIN without ON condition?
See some example in http://www.sitepoint.com/understanding-sql-joins-mysql-database/
You can use 'USING' instead of 'ON' as in the query
SELECT * FROM table1 LEFT JOIN table2 USING (id);
MySQL documentation covers this topic.
Here is a synopsis. When using join
or inner join
, the on
condition is optional. This is different from the ANSI standard and different from almost any other database. The effect is a cross join
. Similarly, you can use an on
clause with cross join
, which also differs from standard SQL.
A cross join creates a Cartesian product -- that is, every possible combination of 1 row from the first table and 1 row from the second. The cross join for a table with three rows ('a', 'b', and 'c') and a table with four rows (say 1, 2, 3, 4) would have 12 rows.
In practice, if you want to do a cross join, then use cross join
:
from A cross join B
is much better than:
from A, B
and:
from A join B -- with no on clause
The on
clause is required for a right or left outer join, so the discussion is not relevant for them.