How to use mysql JOIN without ON condition?

by

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.

More Related Contents:

  1. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  2. Mixing ANSI 1992 JOINs and COMMAs in a query
  3. Difference in MySQL JOIN vs LEFT JOIN
  4. LEFT JOIN only first row
  5. Recreating a query using INNER JOIN? [duplicate]
  6. How can I select data from different tables?
  7. MySQL: Quick breakdown of the types of joins [duplicate]
  8. Join between tables in two different databases?
  9. Difference between left join and right join in SQL Server [duplicate]
  10. MySQL JOIN ON vs USING?
  11. Required to join 2 tables with their FKs in a 3rd table
  12. CROSS/OUTER APPLY in MySQL
  13. a count for each join – optimisation
  14. Are “from Table1 left join Table2” and “from Table2 right join Table1” interchangeable?
  15. MySql Count cannot show 0 values
  16. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  17. MySQL JOIN the most recent row only?
  18. MySQL difference between two rows of a SELECT Statement
  19. MySQL JOIN with LIMIT 1 on joined table
  20. MySQL: How do I join same table multiple times?
  21. Difference between these two joining table approaches?
  22. MYSQL Left Join COUNTS from multiple tables
  23. Laravel join with 3 Tables
  24. MySQL Insert & Joins
  25. MySQL: Inner join vs Where [duplicate]
  26. How to join two tables using a comma-separated-list in the join field
  27. Delete from one table with join
  28. When to use a left outer join?
  29. MySQL cartesian product between two SELECT statements
  30. MySQL Multiple Joins in one query?

Leave a Comment