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 CROSS JOIN. The explicit way is preferred for clarity:

-- Explicit Syntax (Recommended)
SELECT
    table1.column,
    table2.column
FROM
    table1
CROSS JOIN
    table2;

-- Implicit Syntax (Old Style)
SELECT
    table1.column,
    table2.column
FROM
    table1,
    table2;

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.