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.

Practice

1. What does a CROSS JOIN do in MySQL?

easy

A. Joins tables based on matching column values.

B. Joins tables using a condition on a specific column.

C. Returns only rows that exist in both tables.

D. Pairs every row from the first table with every row from the second table.

Solution

1. Step 1: Understand the definition of CROSS JOIN

   A CROSS JOIN returns the Cartesian product of two tables, pairing each row of the first table with every row of the second table.

2. Step 2: Compare with other join types

   Other joins like INNER JOIN or LEFT JOIN use matching conditions, but CROSS JOIN does not use any condition and pairs all rows.

3. Final Answer:

   Pairs every row from the first table with every row from the second table. -> Option D

4. Quick Check:

   CROSS JOIN = all combinations [OK]

Hint: CROSS JOIN = all possible pairs between tables [OK]

Common Mistakes:

• Confusing CROSS JOIN with INNER JOIN
• Thinking CROSS JOIN filters rows
• Assuming CROSS JOIN requires ON condition

2. Which of the following is the correct syntax for a CROSS JOIN in MySQL?

easy

A. SELECT * FROM table1 CROSS JOIN table2;

B. SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;

C. SELECT * FROM table1 INNER JOIN table2 WHERE table1.id = table2.id;

D. SELECT * FROM table1 LEFT JOIN table2 USING (id);

Solution

1. Step 1: Identify the CROSS JOIN syntax

   The correct syntax for CROSS JOIN is: SELECT * FROM table1 CROSS JOIN table2;

2. Step 2: Differentiate from other join syntaxes

   Other options use INNER JOIN or LEFT JOIN with conditions, which are not CROSS JOIN syntax.

3. Final Answer:

   SELECT * FROM table1 CROSS JOIN table2; -> Option A

4. Quick Check:

   Use CROSS JOIN keyword for Cartesian product [OK]

Hint: Use CROSS JOIN keyword without ON clause [OK]

Common Mistakes:

• Using ON clause with CROSS JOIN
• Confusing CROSS JOIN with INNER JOIN syntax
• Using WHERE instead of ON for joins

3. Given two tables:
Table A: id
1
2

Table B: color
'Red'
'Blue'

What is the result of SELECT * FROM A CROSS JOIN B;?

medium

A. Four rows: (1, 'Red'), (1, 'Blue'), (2, 'Red'), (2, 'Blue')

B. Two rows: (1, 'Blue'), (2, 'Red')

C. No rows, because no join condition

D. Two rows: (1, 'Red'), (2, 'Blue')

Solution

1. Step 1: Understand CROSS JOIN output

   CROSS JOIN pairs every row from Table A with every row from Table B, creating all combinations.

2. Step 2: Calculate combinations

   Table A has 2 rows, Table B has 2 rows, so total rows = 2 * 2 = 4. The pairs are (1, 'Red'), (1, 'Blue'), (2, 'Red'), (2, 'Blue').

3. Final Answer:

   Four rows: (1, 'Red'), (1, 'Blue'), (2, 'Red'), (2, 'Blue') -> Option A

4. Quick Check:

   Rows = 2 * 2 = 4 [OK]

Hint: Multiply row counts of both tables for CROSS JOIN rows [OK]

Common Mistakes:

• Assuming CROSS JOIN filters rows
• Counting only matching rows
• Ignoring all combinations

4. Consider this query:
SELECT * FROM employees CROSS JOIN departments ON employees.id = departments.id;
What is the error in this query?

medium

A. The query will return only matching rows between employees and departments.

B. The ON clause is correct and filters rows properly.

C. CROSS JOIN cannot have an ON clause; it causes a syntax error.

D. The query will return no rows because of wrong ON condition.

Solution

1. Step 1: Check CROSS JOIN syntax rules

   CROSS JOIN does not accept an ON clause because it returns all combinations without filtering.

2. Step 2: Identify the error

   Using ON with CROSS JOIN causes a syntax error in MySQL.

3. Final Answer:

   CROSS JOIN cannot have an ON clause; it causes a syntax error. -> Option C

4. Quick Check:

   ON clause invalid with CROSS JOIN [OK]

Hint: No ON clause allowed with CROSS JOIN [OK]

Common Mistakes:

• Adding ON clause to CROSS JOIN
• Confusing CROSS JOIN with INNER JOIN
• Expecting filtered results from CROSS JOIN

5. You have two tables:
Products (product_id, name) with 3 rows,
Colors (color_id, color_name) with 4 rows.
You want to generate a list of all possible product-color combinations.
Which query correctly achieves this?

hard

A. SELECT product_id, color_name FROM Products INNER JOIN Colors ON Products.product_id = Colors.color_id;

B. SELECT product_id, color_name FROM Products CROSS JOIN Colors;

C. SELECT product_id, color_name FROM Products LEFT JOIN Colors ON Products.product_id = Colors.color_id;

D. SELECT product_id, color_name FROM Products JOIN Colors WHERE Products.product_id = Colors.color_id;

Solution

1. Step 1: Understand the goal

   We want all possible combinations of products and colors, which means pairing every product with every color.

2. Step 2: Choose the correct join type

   CROSS JOIN produces the Cartesian product, giving all combinations. INNER JOIN or LEFT JOIN with conditions only return matching rows, which is not desired here.

3. Final Answer:

   SELECT product_id, color_name FROM Products CROSS JOIN Colors; -> Option B

4. Quick Check:

   Use CROSS JOIN for all combinations [OK]

Hint: Use CROSS JOIN to get all product-color pairs [OK]

Common Mistakes:

• Using INNER JOIN expecting all combinations
• Adding ON condition that filters rows
• Using WHERE instead of CROSS JOIN