CROSS JOIN in MySQL - Step-by-Step Execution

Concept Flow - CROSS JOIN

Start with Table A

↓

Start with Table B

↓

Combine each row of A with each row of B

↓

Create new rows for every pair

↓

Result: All possible pairs of rows from A and B

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

Execution Sample

MySQL

SELECT * FROM Colors CROSS JOIN Shapes;

This query pairs each color with each shape, producing all combinations.

Execution Table

Step	Action	Current Row from Colors	Current Row from Shapes	Output Row
1	Pair first row of Colors with first row of Shapes	Red	Circle	(Red, Circle)
2	Pair first row of Colors with second row of Shapes	Red	Square	(Red, Square)
3	Pair first row of Colors with third row of Shapes	Red	Triangle	(Red, Triangle)
4	Pair second row of Colors with first row of Shapes	Blue	Circle	(Blue, Circle)
5	Pair second row of Colors with second row of Shapes	Blue	Square	(Blue, Square)
6	Pair second row of Colors with third row of Shapes	Blue	Triangle	(Blue, Triangle)
7	Pair third row of Colors with first row of Shapes	Green	Circle	(Green, Circle)
8	Pair third row of Colors with second row of Shapes	Green	Square	(Green, Square)
9	Pair third row of Colors with third row of Shapes	Green	Triangle	(Green, Triangle)
10	No more rows in Colors to pair	N/A	N/A	End of CROSS JOIN

💡 All rows from Colors have been paired with all rows from Shapes, completing the CROSS JOIN.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	After 7	After 8	After 9
Current Row from Colors	Red	Red	Red	Blue	Blue	Blue	Green	Green	Green	N/A
Current Row from Shapes	Circle	Square	Triangle	Circle	Square	Triangle	Circle	Square	Triangle	N/A
Output Row	(Red, Circle)	(Red, Square)	(Red, Triangle)	(Blue, Circle)	(Blue, Square)	(Blue, Triangle)	(Green, Circle)	(Green, Square)	(Green, Triangle)	End

Key Moments - 3 Insights

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

Because CROSS JOIN pairs every row from the first table with every row from the second table, so total rows = rows in Table A × rows in Table B, as shown in execution_table rows 1 to 9.

Does CROSS JOIN require any condition to combine rows?

No, CROSS JOIN combines all rows without any condition, unlike INNER or LEFT JOIN. This is clear from the execution_table where every pair is created without filtering.

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 the process early, unlike the example shown.

Visual Quiz - 3 Questions

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

A (Green, Circle)

B (Red, Square)

C (Blue, Square)

D (Blue, Triangle)

💡 Hint

Check the 'Output Row' column in execution_table at step 5.

At which step does the CROSS JOIN finish pairing all rows?

A Step 10

B Step 9

C Step 8

D Step 7

💡 Hint

Look for the step where the exit note says 'End of CROSS JOIN'.

If the Colors table had 4 rows instead of 3, how many output rows would the CROSS JOIN produce?

A 9

B 12

C 7

D 6

💡 Hint

Multiply the number of rows in Colors by the number of rows in Shapes (3 shapes).

Concept Snapshot

CROSS JOIN syntax: SELECT * FROM TableA CROSS JOIN TableB;
It pairs every row of TableA with every row of TableB.
Result rows = rows in TableA × rows in TableB.
No join condition is needed.
Useful for generating all combinations.

Full Transcript

CROSS JOIN takes two tables and pairs each row from the first table with every row from the second table. This creates all possible combinations of rows. For example, if Colors has 3 rows and Shapes has 3 rows, the CROSS JOIN produces 9 rows. The execution table shows each step pairing one color with one shape. The process stops when all pairs are made. CROSS JOIN does not use any condition to filter rows. If one table is empty, the result is empty. This is useful when you want to see every possible combination of two sets of data.