129

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

Alexander T.
  • 1,401
  • 2
  • 9
  • 11
  • 1
    As the `ON` clause tells the server how the tables are related, no. If your different join types all give the same result you're doing it wrong somehow, and adding some code could help us spot your problem. In the mean time head over to [Jeff Atwood's blog](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) for a 2 minute intro to the different join types. – fvu May 09 '13 at 21:04
  • 1
    @fvu That blog post is repudiated by its author in the comments & [is not helpful](https://stackoverflow.com/a/55642928/3404097). Both MySQL & SQLite allow (INNER) JOIN without ON. (Treating it like ON 1=1, ie treating it like CROSS JOIN.) – philipxy Mar 02 '21 at 18:41

3 Answers3

189

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.

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
15

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);
Manu
  • 4,101
  • 1
  • 17
  • 23
  • 7
    For additional clarification, the joining column in both tables must be identically named for USING to work – rmirabelle Feb 02 '16 at 18:26
5

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.

Tony
  • 9,672
  • 3
  • 47
  • 75
Neel Sandell
  • 429
  • 5
  • 12