SQL CROSS JOIN

Videos Discussion

So far, we have studied JOINs where:

Records are matched using a condition (ON)

Rows are linked logically (employee → department, etc.)

But sometimes, the requirement is different.

Imagine a situation where:

You want all possible combinations

There is no direct relationship

Matching is not needed at all

This is where CROSS JOIN comes into picture.

What Is a CROSS JOIN?

A CROSS JOIN:

Combines every row of one table with every row of another table

Produces a Cartesian product

Does not use an ON condition

If table A has m rows and table B has n rows, the result will have m × n rows.

Simple Understanding

Think of:

Shirts (Red, Blue)

Sizes (S, M, L)

If you want all possible shirt–size combinations, you do not match you combine everything. That is exactly what a CROSS JOIN does.

Table

product

product
Laptop
Mobile

colors

color
Black
Silver
White

Example

SELECT 
    p.product,
    c.color
FROM products p
CROSS JOIN colors c;

Output

product	color
Laptop	Black
Laptop	Silver
Laptop	White
Mobile	Black
Mobile	Silver
Mobile	White

2 products × 3 colors = 6 rows

Why CROSS JOIN Exists

CROSS JOIN is not used frequently, but when needed it is very powerful.

Use Case 1: Product Variations

Creating:

Product + Size

Product + Color

Product + Material

SELECT p.product, s.size
FROM products p
CROSS JOIN sizes s;

Used in:

E-commerce catalogs

Inventory planning

Use Case 2: Test Data Generation

You may want:

All combinations of users and roles

All combinations of dates and shifts

SELECT u.username, r.role
FROM users u
CROSS JOIN roles r;

Used in:

Testing

Access-control simulations

Use Case 3: Time Table / Scheduling

Combining:

Days

Time slots

SELECT d.day, t.slot
FROM days d
CROSS JOIN timeslots t;

Used in:

Timetable systems

Resource allocation

Difference Between CROSS JOIN and INNER JOIN

Feature	INNER JOIN	CROSS JOIN
Matching condition	Required	Not required
Output rows	Matching rows	All combinations
NULL handling	Possible	Not applicable
Use case	Logica relation	Combination generation

CROSS JOIN Without Keyword

This also works (not recommended)

SELECT *
FROM products, colors;

This is logically a CROSS JOIN

Less readable

Two Minute Drill

CROSS JOIN combines all rows
No matching condition
Produces Cartesian product
Used for combinations & test data
Can generate huge result sets
Always be intentional