In this lesson, you will learn how to generate every possible combination of rows between two tables using the CROSS JOIN operation.
The Mechanics of a Cartesian Product
A CROSS JOIN creates a Cartesian Product. If you have Table A with 3 rows and Table B with 4 rows, a CROSS JOIN will produce a result set containing 12 rows (3 multiplied by 4).
Mechanically, the database takes the first row of Table A and pairs it with every single row in Table B. It then repeats this process for the second row of Table A, and so on. It makes no attempt to "match" rows based on common keys; it simply forces an exhaustive pairing of every record in the first set with every record in the second.
A Concrete Illustration
Imagine you have a table of Sizes ('S', 'M', 'L') and a table of Colors ('Red', 'Blue'). To generate a catalog of every available shirt variant, you would use:
SELECT Sizes.size, Colors.color
FROM Sizes
CROSS JOIN Colors;
The result would look like this:
| size | color |
|---|---|
| S | Red |
| S | Blue |
| M | Red |
| M | Blue |
| L | Red |
| L | Blue |
When to Apply Them
While you should avoid CROSS JOIN when you intended to filter data (as it can accidentally create massive, useless result sets), it is incredibly powerful for data generation.
You apply it when you need to "fill the gaps" in your data. For example, if you are building a dashboard and need to ensure every product is represented for every day of the month—even if there were no sales on a specific day—you can CROSS JOIN your list of products with a list of dates to create a foundational grid. From there, you can perform an OUTER JOIN to see which slots have actual data and which remain empty.
Key Takeaway: A CROSS JOIN produces a Cartesian product by pairing every row of one table with every row of another, making it a specialized tool for generating comprehensive data combinations rather than linking related records.