SQL CROSS JOIN - Creating All Possible Row Combinations

Introduction

What if you want to generate every possible combination of two datasets — like pairing every student with every subject? That’s what CROSS JOIN does. It’s like making a multiplication grid of rows — every row from the first table is paired with every row from the second.

What is CROSS JOIN?

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.

Syntax

SELECT *
FROM table1
CROSS JOIN table2;

1. Sample Tables – Students and Subjects

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');

2. CROSS JOIN Example

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

3. When to Use CROSS JOIN

• 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.

4. Alternative Syntax (without keyword)

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.

5. Be Cautious with Large Tables

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.

Real-World Use Case

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.

Best Practices

• 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.

Summary

CROSS JOIN gives you raw power — all combinations, no conditions. While rarely used in everyday queries, it’s incredibly helpful in generating bulk mappings, permutations, and assignment matrices.

What’s Next?

Next, we’ll explore UNION and UNION ALL — to combine result sets from multiple SELECT queries into one unified output.