How to use mysql JOIN without ON condition?
Solution 1
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.
Solution 2
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);
Solution 3
There are several ways to do a cross join or cartesian product:
SELECT column_names FROM table1 CROSS JOIN table2;
SELECT column_names FROM table1, table2;
SELECT column_names FROM table1 JOIN table2;
Neglecting the on
condition in the third case is what results in a cross join.
Alexander T.
Updated on January 03, 2022Comments
-
Alexander T. over 2 years
Is it possible to write join query without
ON
statement? and how do these joins differLEFT JOIN, RIGHT JOIN
works. -
rmirabelle over 8 yearsFor additional clarification, the joining column in both tables must be identically named for USING to work