Introduction

A MySQL JOIN without an ON clause is valid SQL, but it does not behave like a typical relational join. In most cases it produces a Cartesian product, meaning every row on the left is paired with every row on the right. This can be useful in controlled scenarios, but dangerous if used accidentally.

What JOIN Without ON Actually Means

When you omit ON, you are usually asking for full pairing between tables, equivalent to CROSS JOIN.

SELECT *
FROM products
CROSS JOIN regions;

If products has 100 rows and regions has 5 rows, the result has 500 rows.

Some developers also write comma joins, which have similar behavior before filtering:

SELECT *
FROM products, regions;

This older syntax is legal, but explicit CROSS JOIN is clearer and easier to review.

Use Cases Where Cartesian Join Is Intentional

There are legitimate cases for this pattern. One common example is generating all combinations of dimensions for reporting.

1CREATE TABLE days (day_name VARCHAR(10));
2INSERT INTO days VALUES ('Mon'), ('Tue'), ('Wed');
3
4CREATE TABLE shifts (shift_name VARCHAR(10));
5INSERT INTO shifts VALUES ('Morning'), ('Evening');
6
7SELECT d.day_name, s.shift_name
8FROM days AS d
9CROSS JOIN shifts AS s
10ORDER BY d.day_name, s.shift_name;

Output gives every day and shift pair, which is useful for building schedule templates.

Another use case is creating test datasets by multiplying small seed tables.

1CREATE TABLE digits (d INT);
2INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
3
4SELECT a.d * 10 + b.d AS num
5FROM digits AS a
6CROSS JOIN digits AS b
7ORDER BY num;

This quickly generates values from 0 to 99 without procedural loops.

Add Filtering with WHERE if Needed

A join without ON can still be restricted later in WHERE. This is functionally similar to an inner join, but many teams prefer explicit ON for readability.

1SELECT o.order_id, c.customer_name
2FROM orders AS o
3JOIN customers AS c
4WHERE o.customer_id = c.customer_id;

The query works, but equivalent explicit syntax is easier to maintain:

SELECT o.order_id, c.customer_name
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id;

Use WHERE filtering mainly when you intentionally start from a Cartesian set and then apply additional logical constraints.

Prefer Explicit Join Semantics in Production

If the goal is relational matching, write the condition in ON even when WHERE would produce the same rows. This makes intent obvious during review and reduces accidental query changes.

1SELECT p.product_name, c.category_name
2FROM products AS p
3JOIN categories AS c ON p.category_id = c.category_id
4WHERE p.is_active = 1;

This style separates relationship logic from row filtering logic, which improves maintainability as queries evolve.

Performance and Safety Considerations

Cartesian joins can explode row counts and consume memory, temp space, and execution time. Use EXPLAIN before running large queries.

1EXPLAIN
2SELECT p.id, r.region_name
3FROM products AS p
4CROSS JOIN regions AS r;

If row estimates are high, add limits for inspection:

1SELECT p.id, r.region_name
2FROM products AS p
3CROSS JOIN regions AS r
4LIMIT 50;

This helps verify structure before running full scans in production data.

Common Pitfalls

A common mistake is forgetting the join condition and unintentionally returning millions of rows. If results look far larger than expected, verify whether an ON clause is missing.

Another issue is using comma join syntax in modern codebases. It still works, but reviewers can miss implicit Cartesian behavior more easily than with explicit CROSS JOIN.

Teams also place relationship predicates only in WHERE and later add additional filters that change semantics by accident. Prefer explicit ON for relational joins and reserve no-ON syntax for intentional Cartesian combinations.

Summary

• A JOIN without ON usually means Cartesian product behavior.
• Use explicit CROSS JOIN when full combinations are intentional.
• Add WHERE filters carefully if you start from a Cartesian set.
• Use explicit ON clauses for relational matching in production queries.
• Run EXPLAIN to check row growth and query cost before large runs.