← Back to Chapters

MySQL CROSS JOIN

? MySQL CROSS JOIN

? Quick Overview

The CROSS JOIN returns the Cartesian product of two tables. Each row from the first table is combined with every row from the second table. It essentially creates every possible pairing between the two datasets.

? Key Concepts

  • Cartesian Product: Produces all possible row combinations.
  • No Join Condition: Unlike INNER JOIN, it does not require an ON or USING clause.
  • Multiplicative Growth: If Table A has 10 rows and Table B has 5, the result will have 50 rows (10 × 5).

? Syntax / Theory

? View SQL Syntax 
// Basic syntax of CROSS JOIN
SELECT column1, column2
FROM table1
CROSS JOIN table2;

? Code Examples

students Table

// Sample students table data
id | name
1  | Alice
2  | Bob

courses Table

// Sample courses table data
id | course
1  | Math
2  | Science

⚙️ Running a CROSS JOIN Query

? View Execution 
// Combine each student with every course
SELECT students.name, courses.course
FROM students
CROSS JOIN courses;

? Live Output / Explanation

// Resulting Cartesian product (4 rows total)
Alice | Math
Alice | Science
Bob   | Math
Bob   | Science

? Interactive Understanding

This grid demonstrates a CROSS JOIN between Users (A, B) and Actions (Login, Logout, Edit):

A + Login
A + Logout
A + Edit
B + Login
B + Logout
B + Edit

? Use Cases

  • E-commerce: Generating all color and size combinations for a product.
  • Scheduling: Finding all possible shifts for all available employees.
  • Testing: Generating large amounts of dummy data for performance stress tests.

✅ Tips & Best Practices

  • Small Tables Only: Be careful! Joining two tables with 1,000 rows each creates 1,000,000 rows.
  • Implicit Joins: SELECT * FROM table1, table2 is often treated as a CROSS JOIN in many SQL dialects.
  • Filtering: You can add a WHERE clause to a CROSS JOIN to turn it into an INNER JOIN.

? Try It Yourself

  • Create colors (Red, Blue) and sizes (S, M, L) tables and join them.
  • Calculate the result count of a CROSS JOIN between a table with 5 rows and a table with 20 rows.
  • Generate all possible 2-digit combinations using a table of numbers (0-9).