How to use mysql JOIN without ON condition?

mysql join left-join inner-join right-join
Alexander T. picture Alexander T. · May 9, 2013 · Viewed 141k times · Source

Is it possible to write join query without ON statement? and how do these joins differ LEFT JOIN, RIGHT JOIN works.

Answer

Gordon Linoff picture Gordon Linoff · May 9, 2013

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.

If you need to understand the different types of joins, then you need to do some studying on relational databases. Stackoverflow is not an appropriate place for that level of discussion.

Related questions

mysql: why does left join not use an index?

I am facing a strange performance issue with a mysql query. SELECT `pricemaster_products`.*, `products`.* FROM `pricemaster_products` LEFT JOIN `products` ON `pricemaster_products`.`ean` = `products`.`products_ean` I explicitely want to use a left join. But the query takes …

Read More
LEFT JOIN only first row

I read many threads about getting only the first row of a left join, but, for some reason, this does not work for me. Here is my structure (simplified of course) Feeds id | title | content ---------------------- 1 | Feed 1 | ... Artists artist_id | …

Read More
SQL LEFT-JOIN on 2 fields for MySQL

I have a view A and a view B. In A I have a lot of information about some systems, like IP and port which I want to preserve all. In B I have just one information that I want …

Read More