CROSS JOIN

Cross Join in MySQL

Introduction
Generating all possible combinations between tables creates cartesian products.
CROSS JOIN produces every row from left table paired with every right table row.
It is widely used for:

• Generating test data
• Matrix operations
• All combinations analysis

What is Cross Join
CROSS JOIN creates cartesian product: M rows × N rows = M×N result rows.
No ON condition required (every combination).

Basic Syntax

sql

SELECT columns 

FROM table1 

CROSS JOIN table2; 

Example

sql

SELECT s.name, c.course_name 

FROM Students s 

CROSS JOIN Courses c; 

Every student paired with every course.

Comma Syntax (Implicit Cross Join)
Traditional syntax (pre-ANSI).

sql

SELECT s.name, c.course_name 

FROM Students s, Courses c; 

Identical to CROSS JOIN.

Cross Join with WHERE
Filter combinations after generation.

sql

SELECT s.name, c.course_name 

FROM Students s 

CROSS JOIN Courses c 

WHERE s.city = c.city; 

Cross Join with Multiple Tables
All possible combinations.

sql

SELECT s.name, c.course_name, d.department_name 

FROM Students s 

CROSS JOIN Courses c 

CROSS JOIN Departments d; 

Cross Join with ORDER BY
Sort the massive result set.

sql

SELECT s.student_id, c.course_id 

FROM Students s 

CROSS JOIN Courses c 

ORDER BY s.student_id, c.course_id; 

Cross Join with LIMIT
Prevent cartesian explosion.

sql

SELECT s.name, c.course_name 

FROM Students s 

CROSS JOIN Courses c 

LIMIT 100; 

Practical Use Case: Number Sequences
Generate date ranges or IDs.

sql

SELECT

    DATE('2025-01-01') + INTERVAL (r.n + c.n * 10) DAY as date

FROM (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) r

CROSS JOIN (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) c;

Cross Join for Test Data
Generate combinations systematically.

sql

SELECT city, status 

FROM Cities 

CROSS JOIN (SELECT 'Active' status UNION SELECT 'Inactive') s; 

Cross Join with Aggregation
Count combinations.

sql

SELECT COUNT(*) as total_combinations 

FROM Students s 

CROSS JOIN Courses c; 

Performance Warning
Cartesian explosion grows exponentially.

text

Students: 1000 rows × Courses: 100 rows = 100,000 result rows

1000 × 1000 = 1,000,000 rows (unmanageable!)

Cross Join vs Other Joins

Join Type	Matches	Rows Generated
CROSS JOIN	All combos	M × N
INNER JOIN	Matching	≤ min(M,N)
LEFT JOIN	Left + matches	M

Execution Order with CROSS JOIN
FROM → CROSS JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Performance Considerations

• Avoid large table CROSS JOINs
• Always use LIMIT/WHERE
• Generate sequences with small tables
• Indexes irrelevant (no join condition)

Important Notes

• No ON condition needed
• Comma syntax = CROSS JOIN
• Result size = row1 × row2 × row3...
• WHERE filters after cartesian generation
• Useful for small lookup tables

Example Scenario
Generate student-course assignment matrix:

sql

SELECT s.name, c.course_name,

       CASE WHEN s.skill_level >= c.required_level

            THEN 'Eligible' ELSE 'Not Eligible' END as status

FROM Students s

CROSS JOIN Courses c

WHERE s.city = c.offered_city

ORDER BY s.name, c.course_name

LIMIT 1000;

Common Mistakes

• No WHERE/LIMIT (memory explosion)
• Large tables (server crash)
• Confusing with INNER JOIN
• Expecting matching behavior
• Performance surprises

Key Points to Remember

• CROSS JOIN = every row × every row
• Comma syntax equivalent
• Cartesian explosion (M×N rows)
• WHERE filters after generation
• Use LIMIT always
• Perfect for small lookups/sequences