How to Use CROSS JOIN in MySQL

Key Insights

CROSS JOIN produces a Cartesian product—every row from the first table paired with every row from the second—making it essential for generating combinations but dangerous with large datasets
Practical use cases include generating product variants, creating reporting skeletons with date ranges, building test data, and producing all possible pairings for comparison analysis
Always calculate expected row counts before executing (table1_rows × table2_rows) and prefer explicit CROSS JOIN syntax over comma-separated tables for clarity and maintainability

Introduction to CROSS JOIN

CROSS JOIN is the most straightforward yet least understood join type in MySQL. While INNER JOIN and LEFT JOIN match rows based on conditions, CROSS JOIN does something fundamentally different: it produces every possible combination of rows between two tables.

This operation creates what mathematicians call a Cartesian product. If table A has 5 rows and table B has 4 rows, the result contains exactly 20 rows—every row from A paired with every row from B.

Most developers avoid CROSS JOIN because they’ve been burned by accidental Cartesian products in poorly written queries. But when you intentionally need combinations, CROSS JOIN is the right tool. The key is knowing when that situation arises.

Basic CROSS JOIN Syntax

MySQL supports two syntaxes for cross joins. The explicit syntax clearly communicates intent:

SELECT *
FROM table_a
CROSS JOIN table_b;

The implicit syntax uses a comma between tables:

SELECT *
FROM table_a, table_b;

Both produce identical results, but the explicit syntax is strongly preferred. It makes your intention unmistakable to anyone reading the query—including your future self.

Here’s a concrete example with colors and sizes:

-- Create sample tables
CREATE TABLE colors (
    color_id INT PRIMARY KEY,
    color_name VARCHAR(20)
);

CREATE TABLE sizes (
    size_id INT PRIMARY KEY,
    size_name VARCHAR(10)
);

INSERT INTO colors VALUES (1, 'Red'), (2, 'Blue'), (3, 'Green');
INSERT INTO sizes VALUES (1, 'Small'), (2, 'Medium'), (3, 'Large');

-- Cross join to see all combinations
SELECT 
    c.color_name,
    s.size_name
FROM colors c
CROSS JOIN sizes s
ORDER BY c.color_id, s.size_id;

This produces nine rows—every color paired with every size:

+------------+-----------+
| color_name | size_name |
+------------+-----------+
| Red        | Small     |
| Red        | Medium    |
| Red        | Large     |
| Blue       | Small     |
| Blue       | Medium    |
| Blue       | Large     |
| Green      | Small     |
| Green      | Medium    |
| Green      | Large     |
+------------+-----------+

Practical Use Cases

Cross joins shine in specific scenarios. Understanding these patterns helps you recognize when CROSS JOIN is the right choice.

Generating Product Variants

E-commerce systems frequently need all possible product combinations. Rather than manually inserting every variant, generate them:

-- Product catalog tables
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    base_price DECIMAL(10,2)
);

CREATE TABLE product_colors (
    color_id INT PRIMARY KEY,
    color_name VARCHAR(30),
    price_modifier DECIMAL(10,2) DEFAULT 0
);

CREATE TABLE product_sizes (
    size_id INT PRIMARY KEY,
    size_code VARCHAR(5),
    price_modifier DECIMAL(10,2) DEFAULT 0
);

INSERT INTO products VALUES 
    (1, 'Classic T-Shirt', 29.99),
    (2, 'Premium Hoodie', 59.99);

INSERT INTO product_colors VALUES 
    (1, 'Black', 0), (2, 'White', 0), (3, 'Navy', 2.00);

INSERT INTO product_sizes VALUES 
    (1, 'XS', -2.00), (2, 'S', 0), (3, 'M', 0), 
    (4, 'L', 0), (5, 'XL', 3.00), (6, '2XL', 5.00);

-- Generate all product variants with calculated prices
SELECT 
    p.product_id,
    p.product_name,
    pc.color_name,
    ps.size_code,
    CONCAT(p.product_name, ' - ', pc.color_name, ' - ', ps.size_code) AS variant_name,
    (p.base_price + pc.price_modifier + ps.price_modifier) AS final_price
FROM products p
CROSS JOIN product_colors pc
CROSS JOIN product_sizes ps
ORDER BY p.product_id, pc.color_id, ps.size_id;

This generates 36 variants (2 products × 3 colors × 6 sizes) with correctly calculated prices.

Creating Reporting Skeletons

Reports often need rows for every date or category, even when no data exists. Cross joins create this foundation:

-- Generate a sales report skeleton for all stores and months
CREATE TABLE stores (
    store_id INT PRIMARY KEY,
    store_name VARCHAR(50)
);

CREATE TABLE report_months (
    month_date DATE PRIMARY KEY
);

INSERT INTO stores VALUES (1, 'Downtown'), (2, 'Mall'), (3, 'Airport');
INSERT INTO report_months VALUES 
    ('2024-01-01'), ('2024-02-01'), ('2024-03-01');

-- Create skeleton with all store/month combinations
SELECT 
    s.store_id,
    s.store_name,
    rm.month_date,
    DATE_FORMAT(rm.month_date, '%Y-%m') AS month_label
FROM stores s
CROSS JOIN report_months rm
ORDER BY s.store_id, rm.month_date;

Pairing Items for Comparison

When you need to compare every item against every other item, CROSS JOIN provides the foundation:

-- Find all possible team matchups (excluding self-matches)
CREATE TABLE teams (
    team_id INT PRIMARY KEY,
    team_name VARCHAR(50)
);

INSERT INTO teams VALUES (1, 'Eagles'), (2, 'Tigers'), (3, 'Bears'), (4, 'Wolves');

SELECT 
    t1.team_name AS home_team,
    t2.team_name AS away_team
FROM teams t1
CROSS JOIN teams t2
WHERE t1.team_id <> t2.team_id
ORDER BY t1.team_id, t2.team_id;

CROSS JOIN with Filtering (WHERE Clause)

Adding a WHERE clause to a CROSS JOIN filters the Cartesian product. This can make it behave like an INNER JOIN:

-- These two queries produce identical results
-- Approach 1: CROSS JOIN with WHERE
SELECT 
    o.order_id,
    c.customer_name
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;

-- Approach 2: Explicit INNER JOIN (preferred)
SELECT 
    o.order_id,
    c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

While functionally equivalent, the INNER JOIN version is vastly superior for several reasons:

Intent is clear—readers immediately understand you’re matching related rows
Join conditions are separated from filter conditions—the ON clause handles relationships, WHERE handles business logic
Optimizer behavior is predictable—modern MySQL handles both similarly, but explicit joins leave no ambiguity

Use CROSS JOIN when you want combinations. Use INNER JOIN when you want matches. Don’t conflate them.

Performance Considerations

CROSS JOIN’s simplicity hides a dangerous trap: exponential growth. The result set size equals the product of input table sizes.

-- Demonstrate row count explosion
CREATE TABLE small_table (id INT);
CREATE TABLE medium_table (id INT);
CREATE TABLE large_table (id INT);

-- Populate with different sizes
INSERT INTO small_table SELECT a.n FROM 
    (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) a;
-- 5 rows

INSERT INTO medium_table SELECT a.n + (b.n * 10) FROM 
    (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
    (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b;
-- 100 rows

INSERT INTO large_table SELECT a.n + (b.n * 10) + (c.n * 100) FROM 
    (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
    (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
    (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c;
-- 1000 rows

-- Check the explosion
SELECT 
    (SELECT COUNT(*) FROM small_table) AS small_count,
    (SELECT COUNT(*) FROM medium_table) AS medium_count,
    (SELECT COUNT(*) FROM small_table CROSS JOIN medium_table) AS cross_small_medium,
    (SELECT COUNT(*) FROM medium_table CROSS JOIN large_table) AS cross_medium_large;

Results:

+-------------+--------------+--------------------+--------------------+
| small_count | medium_count | cross_small_medium | cross_medium_large |
+-------------+--------------+--------------------+--------------------+
|           5 |          100 |                500 |             100000 |
+-------------+--------------+--------------------+--------------------+

A 100-row table crossed with a 1,000-row table produces 100,000 rows. Cross two 10,000-row tables and you’re looking at 100 million rows. Indexes don’t help—there’s no condition to optimize.

Always verify row counts before running cross joins on production data:

SELECT 
    (SELECT COUNT(*) FROM table_a) * (SELECT COUNT(*) FROM table_b) AS expected_rows;

CROSS JOIN with Derived Tables and CTEs

Cross joins become particularly powerful when combined with generated data sets. CTEs (Common Table Expressions) make this pattern readable:

-- Create a reporting skeleton for the next 7 days across all categories
WITH date_range AS (
    SELECT CURDATE() + INTERVAL n DAY AS report_date
    FROM (
        SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 
        UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
    ) numbers
),
categories AS (
    SELECT 'Electronics' AS category
    UNION SELECT 'Clothing'
    UNION SELECT 'Home & Garden'
    UNION SELECT 'Sports'
)
SELECT 
    dr.report_date,
    c.category,
    0 AS placeholder_sales
FROM date_range dr
CROSS JOIN categories c
ORDER BY dr.report_date, c.category;

This generates 28 rows (7 days × 4 categories) ready to be left-joined with actual sales data, ensuring every date/category combination appears in reports.

For more complex date generation in MySQL 8.0+, use recursive CTEs:

WITH RECURSIVE date_series AS (
    SELECT DATE('2024-01-01') AS dt
    UNION ALL
    SELECT dt + INTERVAL 1 DAY
    FROM date_series
    WHERE dt < DATE('2024-01-31')
)
SELECT 
    ds.dt AS report_date,
    r.region_name
FROM date_series ds
CROSS JOIN (SELECT 'North' AS region_name UNION SELECT 'South' UNION SELECT 'East' UNION SELECT 'West') r
ORDER BY ds.dt, r.region_name;

Summary and Best Practices

CROSS JOIN is a precision tool. Use it deliberately and carefully:

Use explicit syntax—Write CROSS JOIN, not comma-separated tables. Future readers will thank you.
Calculate before executing—Always know the expected row count. Multiply the table sizes and verify it’s reasonable.
Keep input sets small—Cross joins work best with lookup tables, generated sequences, and small reference data. Avoid crossing transactional tables.
Don’t simulate other joins—If you need matching rows, use INNER JOIN. If you need all rows from one side, use LEFT JOIN. CROSS JOIN is for combinations.
Combine with CTEs for clarity—When generating data sets, CTEs make the query structure obvious and maintainable.
Test with LIMIT first—When uncertain, add LIMIT 100 to preview results before fetching the full set.

CROSS JOIN fills a specific niche: generating every possible combination between data sets. Master this pattern and you’ll find elegant solutions to problems that would otherwise require procedural code or manual data entry.