SQL Cross Join

A CROSS JOIN is an SQL operation that returns the Cartesian product of the two joined tables.

Unlike other joins (INNER, LEFT, or RIGHT), a CROSS JOIN does not use an ON clause to look for matching columns. Instead, it pairs every single row from the first table with every single row from the second table.

What is a Cartesian Product?

A Cartesian product is a mathematical operation that multiplies two sets. If Table A has X rows and Table B has Y rows, the resulting table will have exactly X × Y rows.

For example, if you cross-join a table of 5 items with a table of 10 items, your output will contain exactly 50 rows.

Basic Syntax Blueprint

Because there are no matching conditions, the syntax is very simple. There are two standard formats:

Method A: Explicit Syntax (Recommended)

SELECT t1.column1, t2.column2
FROM table1 AS t1
CROSS JOIN table2 AS t2;

Method B: Implicit Syntax (Comma Separated)

Omitting the join keyword and separating table names with a comma accomplishes the exact same result.

SELECT t1.column1, t2.column2
FROM table1 AS t1, table2 AS t2;

A Real-World Walkthrough

Imagine a clothing store setting up a product matrix for shirts (Table A) and sizes (Table B).

shirts Table

sizes Table

The Cross Join Query:

SELECT s.shirt_style, z.size_code
FROM shirts AS s
CROSS JOIN sizes AS z;

The Output Result (2 × 3 = 6 combinations):

Practical Business Use Cases

While accidentally triggers a CROSS JOIN on large production tables can easily crash a database by generating billions of unwanted rows, it is highly useful when used intentionally:

• Generating Combinations: Perfect for creating stock-keeping units (SKUs) based on product colors, styles, and sizes.
• Creating Master Calendars: Cross-joining a table of employees with a table of dates to generate a blank schedule grid for attendance tracking.

⚠️ The Dangerous "Accidental" Cross Join

If you write a standard INNER JOIN or an implicit comma-separated query but forget to write the ON or WHERE filter, your database engine will fall back on a CROSS JOIN.

-- ❌ DANGEROUS ACCIDENT (Forgetting the filter matches every customer to every order)
SELECT c.name, o.order_id
FROM customers c, orders o; -- Generates a massive Cartesian Product!