Can MySQL JOIN Queries Work Without an ON Clause?

MySQL JOIN Queries without ON Conditions

Question: Can MySQL JOIN queries be written without an ON statement, and how do they differ from LEFT and RIGHT JOINs?

Answer:

Yes, MySQL allows JOIN queries to be written without an ON condition. This behavior departs from ANSI SQL standards and most other databases. However, such queries are equivalent to a cross join, which creates a Cartesian product rather than joining rows based on common conditions.

When an ON clause is absent from a JOIN or INNER JOIN in MySQL, the effect is a cross join. Similarly, an ON clause can be used with a CROSS JOIN to further refine the join conditions.

Differences from LEFT and RIGHT JOINs:

LEFT and RIGHT JOINs require an ON clause to specify the join conditions. This is because their purpose is to include rows from the left table or the right table even if there are no matching rows in the other table. Without an ON clause, they would simply return the rows from the left and right tables separately, respectively.

Practical Considerations:

While it is technically possible to use JOIN queries without ON conditions in MySQL, it is generally advisable to use CROSS JOIN explicitly for clarity. The following syntax is preferred:

from A cross join B

over:

from A, B

or:

from A join B -- with no on clause

The above is the detailed content of Can MySQL JOIN Queries Work Without an ON Clause?. For more information, please follow other related articles on the PHP Chinese website!