What Happens in MySQL JOIN Queries When the ON Condition is Omitted?

MySQL JOIN Queries Without ON Conditions

In MySQL, JOIN queries do not strictly require an ON condition. This differs from the ANSI standard and many other databases.

Cross Join

Without an ON condition, a JOIN is interpreted as a cross join. This operation produces a Cartesian product, resulting in a set of records that contains every possible combination of rows from the joined tables. For instance, a cross join between two tables with 3 and 4 rows respectively would generate 12 rows.

Using Cross Join Explicitly

It is recommended to use the CROSS JOIN keyword when explicitly performing a cross join for clarity:

SELECT * FROM A CROSS JOIN B;

INNER JOIN vs. Cross Join

The absence of an ON condition in a JOIN query effectively makes it equivalent to an INNER JOIN with no conditions. This can be useful for finding all matching rows between tables, similar to an INNER JOIN.

Left and Right Outer Joins

For LEFT and RIGHT OUTER JOINs, the ON condition is mandatory, as these types of joins require a specified condition for row matching. Therefore, the absence of an ON condition is not relevant for them.

The above is the detailed content of What Happens in MySQL JOIN Queries When the ON Condition is Omitted?. For more information, please follow other related articles on the PHP Chinese website!