SQL CROSS JOIN — Creating All Possible Row Combinations

Every student must be scheduled for both Maths and Science. The office needs all student-subject combinations.

A CROSS JOIN pairs each row from one source with every row from the other. With m and n rows, the unfiltered result has m × n pairs.

Unlike a matching-key join, the cross product does not use an equality relationship to reduce pairs. Three students and two subjects produce six combinations. Subsequent filtering can reduce those pairs, but the conceptual relationship remains all combinations.

Related concepts: INNER JOIN and Group and Summarize.

A CROSS JOIN produces the Cartesian product of two tables — i.e., it returns all possible combinations of rows. If the first table has m rows and the second has n rows, the result will have m × n rows.

SQL · portable concepts; preserved examples identify their engine

SELECT *
FROM table1
CROSS JOIN table2;

SQL · portable concepts; preserved examples identify their engine

students

CREATE TABLE students (
  student_id INT,
  name VARCHAR(50)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma'),
(2, 'Diya Iyer'),
(3, 'Sneha Patil');

subjects

CREATE TABLE subjects (
  subject_id INT,
  subject_name VARCHAR(30)
);

INSERT INTO subjects VALUES
(101, 'Maths'),
(102, 'Science');

SQL · portable concepts; preserved examples identify their engine

Pair every student with every subject:

SELECT s.name, sub.subject_name
FROM students s
CROSS JOIN subjects sub;

name           | subject_name
----------------+---------------
Aarav Sharma   | Maths
Aarav Sharma   | Science
Diya Iyer      | Maths
Diya Iyer      | Science
Sneha Patil    | Maths
Sneha Patil    | Science

• Generating assignment grids — e.g., all student–subject pairs.
• Simulating combinations — e.g., testing permutations in datasets.
• Creating templates — e.g., monthly schedules across departments.

SQL · portable concepts; preserved examples identify their engine

Some databases allow CROSS JOIN without writing the keyword:

SELECT s.name, sub.subject_name
FROM students s, subjects sub;

But for clarity and compatibility, prefer using the CROSS JOIN keyword.

Because it multiplies all rows, a CROSS JOIN between large tables can quickly become huge and slow. Always verify the expected number of rows before running the query.

A school admin might want to pre-generate exam assignment sheets for every student in every subject, before marks are entered. CROSS JOIN helps create this “template” table.

• Use CROSS JOIN only when all combinations are needed.
• Always preview row counts to avoid large Cartesian products.
• Add WHERE filters afterward if needed to reduce the result set.

An unintended cross product can make a report much larger and distort counts. An empty input gives no pairs. The optimizer may avoid materializing an enormous intermediate product, so a conceptual row count is not a claim about a specific physical execution.

Pair each student with the two subject rows and count the resulting combinations.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_subjects (subject VARCHAR(30));
INSERT INTO practice_subjects VALUES ('Maths'),('Science');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_subjects (subject VARCHAR(30));
INSERT INTO practice_subjects VALUES ('Maths'),('Science');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT COUNT(*) AS combinations FROM practice_students CROSS JOIN practice_subjects;

There are three students and two subjects, so all combinations number 3 × 2 = 6.

A CROSS JOIN pairs each row from one source with every row from the other. With m and n rows, the unfiltered result has m × n pairs.

There are three students and two subjects, so all combinations number 3 × 2 = 6.

Continue your understanding with INNER JOIN and Group and Summarize.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.