CROSS JOIN cartesian product in SQL - Step-by-Step Execution

Concept Flow - CROSS JOIN cartesian product

Start with Table A

↓

Start with Table B

↓

Pair each row of A with each row of B

↓

Create combined rows for every pair

↓

Result: All possible combinations

↓

End

CROSS JOIN pairs every row from the first table with every row from the second table, creating all possible combinations.

Execution Sample

SQL

SELECT * FROM Colors CROSS JOIN Shapes;

This query combines every color with every shape, producing all color-shape pairs.

Execution Table

Step	Action	Colors Row	Shapes Row	Output Row
1	Take first row from Colors	Red	-	-
2	Pair with first row from Shapes	Red	Circle	(Red, Circle)
3	Pair with second row from Shapes	Red	Square	(Red, Square)
4	Pair with third row from Shapes	Red	Triangle	(Red, Triangle)
5	Take second row from Colors	Blue	-	-
6	Pair with first row from Shapes	Blue	Circle	(Blue, Circle)
7	Pair with second row from Shapes	Blue	Square	(Blue, Square)
8	Pair with third row from Shapes	Blue	Triangle	(Blue, Triangle)
9	Take third row from Colors	Green	-	-
10	Pair with first row from Shapes	Green	Circle	(Green, Circle)
11	Pair with second row from Shapes	Green	Square	(Green, Square)
12	Pair with third row from Shapes	Green	Triangle	(Green, Triangle)
13	No more rows in Colors	-	-	Stop

💡 All rows from Colors paired with all rows from Shapes, no more rows left in Colors.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	After 7	After 8	After 9	After 10	After 11	After 12	Final
Colors Row	None	Red	Red	Red	Red	Blue	Blue	Blue	Blue	Green	Green	Green	Green	None
Shapes Row	None	None	Circle	Square	Triangle	None	Circle	Square	Triangle	None	Circle	Square	Triangle	None
Output Row	None	None	(Red, Circle)	(Red, Square)	(Red, Triangle)	None	(Blue, Circle)	(Blue, Square)	(Blue, Triangle)	None	(Green, Circle)	(Green, Square)	(Green, Triangle)	None

Key Moments - 3 Insights

Why does the number of output rows equal the product of rows in both tables?

Because each row in the first table pairs with every row in the second table, as shown in execution_table rows 2-4 for the first Colors row.

Is there any filtering or matching condition in CROSS JOIN?

No, CROSS JOIN pairs all rows without any condition, which is why every combination appears in the output as seen in all pairing steps.

What happens if one table is empty?

If either table has zero rows, the CROSS JOIN result is empty because there are no rows to pair, which would stop execution early (not shown here).

Visual Quiz - 3 Questions

Look at the execution_table, what is the output row at step 7?

A (Red, Square)

B (Blue, Square)

C (Green, Circle)

D (Blue, Triangle)

💡 Hint

Check the Output Row column at step 7 in the execution_table.

At which step does the CROSS JOIN start pairing rows from the second Colors row?

A Step 4

B Step 6

C Step 5

D Step 7

💡 Hint

Look at the Colors Row column to see when it changes from 'Red' to 'Blue'.

If the Shapes table had only 2 rows, how many output rows would there be for 3 Colors rows?

A 6

B 5

C 3

D 2

💡 Hint

Multiply the number of rows in Colors by the number of rows in Shapes.

Concept Snapshot

CROSS JOIN syntax: SELECT * FROM table1 CROSS JOIN table2;
It pairs every row of table1 with every row of table2.
Result rows = rows in table1 × rows in table2.
No filtering or matching conditions applied.
Useful for generating all combinations.

Full Transcript

CROSS JOIN creates a Cartesian product of two tables by pairing each row from the first table with every row from the second table. For example, if Colors has 3 rows and Shapes has 3 rows, the result will have 9 rows. The execution steps show how each Colors row is paired with all Shapes rows one by one. This join does not filter or match rows; it simply combines all possible pairs. If either table is empty, the result is empty. This is useful when you want to generate all combinations of two sets of data.