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.

Practice

1. What does a CROSS JOIN do in SQL?

easy

A. It returns all possible combinations of rows from two tables.

B. It returns only matching rows based on a condition.

C. It deletes rows from both tables.

D. It updates rows in one table based on another.

Solution

1. Step 1: Understand the definition of CROSS JOIN

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

2. Step 2: Compare with other join types

   Unlike INNER JOIN or LEFT JOIN, CROSS JOIN does not require a matching condition and returns the Cartesian product.

3. Final Answer:

   It returns all possible combinations of rows from two tables. -> Option A

4. Quick Check:

   CROSS JOIN = all combinations [OK]

Hint: CROSS JOIN = multiply rows from both tables [OK]

Common Mistakes:

• Confusing CROSS JOIN with INNER JOIN
• Thinking CROSS JOIN filters rows
• Assuming CROSS JOIN deletes or updates data

2. Which of the following is the correct syntax to perform a CROSS JOIN between tables Employees and Departments?

easy

A. SELECT * FROM Employees INNER JOIN Departments;

B. SELECT * FROM Employees JOIN Departments ON Employees.id = Departments.id;

C. SELECT * FROM Employees CROSS JOIN Departments;

D. SELECT * FROM Employees WHERE CROSS JOIN Departments;

Solution

1. Step 1: Identify correct CROSS JOIN syntax

   The correct syntax uses the keyword CROSS JOIN between two table names without any ON condition.

2. Step 2: Eliminate incorrect options

   SELECT * FROM Employees JOIN Departments ON Employees.id = Departments.id; uses JOIN with ON condition (not CROSS JOIN). SELECT * FROM Employees INNER JOIN Departments; uses INNER JOIN without ON, which is invalid. SELECT * FROM Employees WHERE CROSS JOIN Departments; uses WHERE incorrectly.

3. Final Answer:

   SELECT * FROM Employees CROSS JOIN Departments; -> Option C

4. Quick Check:

   Correct CROSS JOIN syntax = SELECT * FROM Employees CROSS JOIN Departments; [OK]

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

Common Mistakes:

• Using JOIN without ON for CROSS JOIN
• Trying to use WHERE for joining tables
• Confusing INNER JOIN with CROSS JOIN syntax

3. Given two tables:
Colors with rows: Red, Blue
Shapes with rows: Circle, Square, Triangle
What will be the result of:
SELECT Colors.color, Shapes.shape FROM Colors CROSS JOIN Shapes;

medium

A. [('Red', 'Circle'), ('Red', 'Square'), ('Red', 'Triangle'), ('Blue', 'Circle'), ('Blue', 'Square'), ('Blue', 'Triangle')]

B. [('Red', 'Circle'), ('Blue', 'Square'), ('Red', 'Triangle')]

C. [('Red', 'Circle'), ('Blue', 'Circle'), ('Red', 'Square'), ('Blue', 'Square')]

D. Empty result set

Solution

1. Step 1: Count rows in each table

   Colors has 2 rows, Shapes has 3 rows.

2. Step 2: Calculate CROSS JOIN result

   CROSS JOIN returns all pairs: 2 * 3 = 6 rows combining each color with each shape.

3. Final Answer:

   [('Red', 'Circle'), ('Red', 'Square'), ('Red', 'Triangle'), ('Blue', 'Circle'), ('Blue', 'Square'), ('Blue', 'Triangle')] -> Option A

4. Quick Check:

   2 colors * 3 shapes = 6 pairs [OK]

Hint: Multiply row counts for CROSS JOIN result size [OK]

Common Mistakes:

• Listing only matching pairs instead of all combinations
• Confusing CROSS JOIN with INNER JOIN
• Ignoring total number of rows in result

4. Consider this SQL query:
SELECT * FROM A CROSS JOIN B WHERE A.id = B.id;
What is the main issue with this query?

medium

A. It will cause a syntax error because CROSS JOIN cannot have WHERE clause.

B. It updates tables A and B unintentionally.

C. It returns no rows because CROSS JOIN filters all rows.

D. It produces the same result as INNER JOIN but less efficiently.

Solution

1. Step 1: Understand CROSS JOIN with WHERE filter

   CROSS JOIN creates all pairs, then WHERE filters pairs where A.id = B.id.

2. Step 2: Compare with INNER JOIN

   This is equivalent to INNER JOIN on A.id = B.id but less efficient because CROSS JOIN generates many unnecessary pairs first.

3. Final Answer:

   It produces the same result as INNER JOIN but less efficiently. -> Option D

4. Quick Check:

   CROSS JOIN + WHERE = INNER JOIN behavior [OK]

Hint: CROSS JOIN + WHERE = INNER JOIN but slower [OK]

Common Mistakes:

• Thinking WHERE is invalid with CROSS JOIN
• Assuming CROSS JOIN filters rows automatically
• Confusing SELECT with UPDATE statements

5. You have two tables:
Products with 4 rows and Colors with 5 rows.
You want to generate a list of all product-color combinations but exclude combinations where the product is discontinued.
Which query correctly uses CROSS JOIN and filtering?

hard

A. SELECT p.name, c.color FROM Products p INNER JOIN Colors c ON p.discontinued = FALSE;

B. SELECT p.name, c.color FROM Products p CROSS JOIN Colors c WHERE p.discontinued = FALSE;

C. SELECT p.name, c.color FROM Products p CROSS JOIN Colors c ON p.discontinued = FALSE;

D. SELECT p.name, c.color FROM Products p, Colors c WHERE p.discontinued = TRUE;

Solution

1. Step 1: Use CROSS JOIN to get all combinations

   Use CROSS JOIN between Products and Colors to get every product-color pair.

2. Step 2: Filter out discontinued products

   Apply WHERE clause to keep only products where discontinued = FALSE.

3. Step 3: Check other options for errors

   SELECT p.name, c.color FROM Products p INNER JOIN Colors c ON p.discontinued = FALSE; uses INNER JOIN incorrectly with ON condition unrelated to join keys. SELECT p.name, c.color FROM Products p CROSS JOIN Colors c ON p.discontinued = FALSE; uses ON with CROSS JOIN, which is invalid syntax. SELECT p.name, c.color FROM Products p, Colors c WHERE p.discontinued = TRUE; filters discontinued = TRUE, opposite of requirement.

4. Final Answer:

   SELECT p.name, c.color FROM Products p CROSS JOIN Colors c WHERE p.discontinued = FALSE; -> Option B

5. Quick Check:

   CROSS JOIN + WHERE filters correctly [OK]

Hint: Filter after CROSS JOIN with WHERE clause [OK]

Common Mistakes:

• Using ON clause with CROSS JOIN
• Filtering with wrong discontinued value
• Confusing INNER JOIN with CROSS JOIN usage