MySQL CROSS JOIN

A CROSS JOIN in MySQL returns every possible combination of rows from two tables. It produces a result known as the Cartesian product — if the first table has 3 rows and the second has 4 rows, the result has 3 × 4 = 12 rows. No ON condition is used in a CROSS JOIN.

When to Use CROSS JOIN

Cross joins are useful when all combinations of two sets are needed — for example, generating every combination of sizes and colours for a clothing product, or pairing every menu item with every table in a restaurant.

Syntax

SELECT table1.column, table2.column
FROM table1
CROSS JOIN table2;

Example: Sizes and Colours

CREATE TABLE sizes (
    size_name VARCHAR(10)
);

INSERT INTO sizes VALUES ('Small');
INSERT INTO sizes VALUES ('Medium');
INSERT INTO sizes VALUES ('Large');

CREATE TABLE colours (
    colour_name VARCHAR(20)
);

INSERT INTO colours VALUES ('Red');
INSERT INTO colours VALUES ('Blue');
INSERT INTO colours VALUES ('Green');

SELECT sizes.size_name, colours.colour_name
FROM sizes
CROSS JOIN colours;

Result (3 × 3 = 9 rows):

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

Every size is paired with every colour.

CROSS JOIN with WHERE (Filtered Cross Join)

Adding a WHERE condition to a CROSS JOIN restricts the combinations returned. This effectively behaves like an INNER JOIN.

SELECT s.size_name, c.colour_name
FROM sizes AS s
CROSS JOIN colours AS c
WHERE c.colour_name = 'Red';

Result:

size_name | colour_name
----------+------------
Small     | Red
Medium    | Red
Large     | Red

Practical Example: Generating a Schedule

CREATE TABLE teachers (
    teacher_name VARCHAR(100)
);
INSERT INTO teachers VALUES ('Ms. Sharma');
INSERT INTO teachers VALUES ('Mr. Verma');

CREATE TABLE subjects (
    subject_name VARCHAR(100)
);
INSERT INTO subjects VALUES ('Maths');
INSERT INTO subjects VALUES ('Science');
INSERT INTO subjects VALUES ('English');

SELECT teacher_name, subject_name
FROM teachers
CROSS JOIN subjects;

Result (2 × 3 = 6 rows):

teacher_name | subject_name
-------------+-------------
Ms. Sharma   | Maths
Ms. Sharma   | Science
Ms. Sharma   | English
Mr. Verma    | Maths
Mr. Verma    | Science
Mr. Verma    | English

Warning: Large Tables

A CROSS JOIN on large tables produces an enormous result. For example, 1000 rows × 1000 rows = 1,000,000 rows. Always use CROSS JOIN only when all combinations are genuinely needed or filter the results with WHERE.

CROSS JOIN vs INNER JOIN

Key Points

• CROSS JOIN produces the Cartesian product of two tables.
• No ON condition is used — all rows from both tables are combined.
• Result count = number of rows in table 1 × number of rows in table 2.
• Adding a WHERE filter limits the combinations returned.
• Avoid CROSS JOIN on large tables without filtering — it can return millions of rows.