Lesson 5.6: CROSS JOIN - The Cartesian Product

While most joins require a specific matching condition (the ON clause), a CROSS JOIN is different. It returns every possible combination of rows from the joined tables. This result is mathematically known as a Cartesian Product.

What is a CROSS JOIN?

A CROSS JOIN produces a result set where the number of rows is the result of multiplying the number of rows in the first table by the number of rows in the second table. No condition is used to match rows; every row in Table A simply meets every row in Table B.

Visualization:

   Table A (Colors)             Table B (Sizes)
   +-----------+                +-----------+
   | color     |                | size      |
   +-----------+                +-----------+
   | Red       |  --\           | Small     |
   | Blue      |  ---|------>   | Medium    |
   +-----------+  --/           | Large     |
                                +-----------+

   Result (Combinations):
   Red, Small
   Red, Medium
   Red, Large
   Blue, Small
   Blue, Medium
   Blue, Large

If Table A has 2 rows and Table B has 3 rows, the result will have 2 x 3 = 6 rows.

CROSS JOIN Syntax

There are two ways to write a Cartesian product:

-- Explicit syntax
SELECT
    table1.column,
    table2.column
FROM
    table1
CROSS JOIN
    table2;

-- Implicit comma syntax
SELECT
    table1.column,
    table2.column
FROM
    table1,
    table2;

It is important to understand that these forms differ not only in style, but also in operator precedence.

• An explicit JOIN has higher precedence and is processed in the same sequence as other JOIN clauses in the query text.
• The comma syntax has lower precedence and is effectively processed only after explicit JOINs.

Because of this, mixing comma joins and explicit JOINs in the same FROM clause can change the meaning of the query or even lead to an error.

For example, the following query is invalid:

FROM a, b JOIN c ON (a.x = c.y AND b.z = c.t)

Why this is an error:

• first, the explicit b JOIN c is processed;
• at that point, table a is not yet in scope for that join expression;
• therefore, the reference to a.x inside the ON clause is invalid.

That is why explicit CROSS JOIN is usually preferable: not simply because it looks clearer, but because it fits better with other JOINs and avoids hidden precedence issues.

Warning: Be extremely careful when using CROSS JOIN on large tables. Joining two tables with 1,000 rows each will produce 1,000,000 rows!

Practical Examples (Sakila Database)

1. Generating All Possible Combinations

Imagine we want to create a report or a grid that shows every film category for every store, even if that store doesn't currently have movies in that category.

SELECT
    s.store_id,
    c.name AS category_name
FROM
    store AS s
CROSS JOIN
    category AS c;

This produces a list of all categories for Store 1, followed by all categories for Store 2.

2. Creating Test Data or Matrices

CROSS JOIN is often used to generate large sets of permutations for testing or for building calendars/schedules where you need to see all time slots against all users.

When to Use CROSS JOIN

• Generating Permutations: When you need a list of every possible combination (e.g., all product colors vs. all product sizes).
• Filling Gaps: When used with LEFT JOIN, it can help identify missing combinations in your data.
• Reporting: To create the "skeleton" of a report that must include all categories, even those with zero values.

Key Takeaways from This Lesson

• CROSS JOIN returns the Cartesian Product of two tables.
• It does not use an ON clause (no matching condition).
• The number of rows in the result is the product of the row counts of both tables.
• Use it with caution on large datasets to avoid performance issues.