CROSS JOIN behavior in PostgreSQL - Step-by-Step Execution

Concept Flow - CROSS JOIN behavior

Start with Table A

↓

Start with Table B

↓

Combine each row of A with every row of B

↓

Result: All possible pairs of rows

↓

Output the combined rows

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

Execution Sample

PostgreSQL

SELECT * FROM A CROSS JOIN B;

This query returns every combination of rows from tables A and B.

Execution Table

Step	Table A Row	Table B Row	Action	Output Row
1	A1	B1	Combine A1 with B1	(A1, B1)
2	A1	B2	Combine A1 with B2	(A1, B2)
3	A2	B1	Combine A2 with B1	(A2, B1)
4	A2	B2	Combine A2 with B2	(A2, B2)
5	End	End	No more rows to combine	Query complete

💡 All rows from A combined with all rows from B; no rows left to pair.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
Current A Row	None	A1	A1	A2	A2	End
Current B Row	None	B1	B2	B1	B2	End
Output Rows Count	0	1	2	3	4	4

Key Moments - 2 Insights

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

Because CROSS JOIN pairs each row of the first table with every row of the second table, so total output rows = rows in A × rows in B, as shown in execution_table rows 1-4.

Does CROSS JOIN filter or exclude any rows?

No, CROSS JOIN does not filter rows; it combines all rows from both tables without conditions, as seen in every step producing an output row.

Visual Quiz - 3 Questions

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

A (A1, B2)

B (A2, B1)

C (A2, B2)

D (A1, B1)

💡 Hint

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

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

A Step 4

B Step 3

C Step 5

D Step 2

💡 Hint

Look at the 'Action' column in execution_table where it says 'Query complete'.

If Table A had 3 rows and Table B had 2 rows, how many output rows would the CROSS JOIN produce?

A 6

B 5

C 3

D 2

💡 Hint

Recall from key_moments that output rows = rows in A × rows in B.

Concept Snapshot

CROSS JOIN syntax: SELECT * FROM A CROSS JOIN B;
Behavior: Combines every row of A with every row of B.
Output rows count = rows in A × rows in B.
No filtering or conditions applied.
Useful for generating all possible pairs.

Full Transcript

CROSS JOIN in SQL combines every row from the first table with every row from the second table. This means if Table A has 2 rows and Table B has 2 rows, the result will have 4 rows, each representing a unique pair from A and B. The execution table shows step-by-step how each row from A is paired with each row from B. CROSS JOIN does not filter or exclude any rows; it simply creates all possible combinations. This behavior is useful when you want to generate all pairs of data from two tables.

Practice

1. What does a CROSS JOIN do in PostgreSQL?

easy

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

B. Returns only matching rows based on a condition.

C. Joins tables based on a common column with duplicates removed.

D. Filters rows from the first table that exist in the second table.

Solution

1. Step 1: Understand CROSS JOIN concept

   A CROSS JOIN creates combinations of all rows from both tables without any condition.

2. Step 2: Compare with other JOIN types

   Unlike INNER JOIN or LEFT JOIN, CROSS JOIN does not filter or match rows but pairs all possible combinations.

3. Final Answer:

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

4. Quick Check:

   CROSS JOIN = all combinations [OK]

Hint: Remember: CROSS JOIN multiplies rows from both tables [OK]

Common Mistakes:

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

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

easy

A. SELECT * FROM table1 CROSS JOIN table2;

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

C. SELECT * FROM table1 INNER CROSS JOIN table2;

D. SELECT * FROM table1 CROSS JOIN ON table2;

Solution

1. Step 1: Recall CROSS JOIN syntax

   The correct syntax is SELECT * FROM table1 CROSS JOIN table2; without any ON clause.

2. Step 2: Identify incorrect options

   SELECT * FROM table1 JOIN table2 ON table1.id = table2.id; uses INNER JOIN with ON clause, C has invalid keyword INNER CROSS JOIN, A has misplaced ON keyword.

3. Final Answer:

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

4. Quick Check:

   Syntax for CROSS JOIN = SELECT * FROM table1 CROSS JOIN table2; [OK]

Hint: CROSS JOIN syntax has no ON clause [OK]

Common Mistakes:

• Adding ON clause with CROSS JOIN
• Using INNER CROSS JOIN which is invalid
• Misplacing keywords in the query

3. You have two tables:
Colors:
color_id | color_name
1 | Red
2 | Blue

Shapes:
shape_id | shape_name
1 | Circle
2 | Square
3 | Triangle

How many rows will this query return?
SELECT color_name, shape_name FROM Colors CROSS JOIN Shapes;

easy

A. 5

B. 3

C. 2

D. 6

Solution

1. Step 1: Count rows in each table

   Colors has 2 rows, Shapes has 3 rows.

2. Step 2: Calculate CROSS JOIN result size

   Result rows = 2 x 3 = 6 rows, all combinations of colors and shapes.

3. Final Answer:

   6 -> Option D

4. Quick Check:

   2 x 3 = 6 rows [OK]

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

Common Mistakes:

• Adding rows instead of multiplying
• Ignoring one table's row count
• Confusing CROSS JOIN with INNER JOIN

4. Given two tables:
Table A:
id | name
1 | Alice
2 | Bob

Table B:
code
X
Y

What is the result of:
SELECT A.name, B.code FROM A CROSS JOIN B;

medium

A. [('Alice', 'X'), ('Bob', 'Y')]

B. [('Alice', 'X'), ('Alice', 'Y'), ('Bob', 'X'), ('Bob', 'Y')]

C. [('Alice', 'X'), ('Bob', 'X'), ('Alice', 'Y'), ('Bob', 'Y')]

D. [('Alice', 'X'), ('Bob', 'X')]

Solution

1. Step 1: Understand CROSS JOIN output

   Each row in A pairs with every row in B, so 2 rows in A x 2 rows in B = 4 rows total.

2. Step 2: List all combinations

   Pairs are (Alice, X), (Alice, Y), (Bob, X), (Bob, Y) in that order.

3. Final Answer:

   [('Alice', 'X'), ('Alice', 'Y'), ('Bob', 'X'), ('Bob', 'Y')] -> Option B

4. Quick Check:

   2 x 2 = 4 rows [OK]

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

Common Mistakes:

• Assuming CROSS JOIN filters rows
• Mixing order of pairs incorrectly
• Counting only matching rows instead of all combinations

5. Consider this query:
SELECT * FROM table1 CROSS JOIN table2 ON table1.id = table2.id;
What is the problem with this query?

medium

A. It behaves like INNER JOIN with ON condition.

B. It will return only matching rows based on id.

C. Syntax error because CROSS JOIN does not allow ON clause.

D. It returns all rows from table1 only.

Solution

1. Step 1: Check CROSS JOIN syntax rules

   CROSS JOIN syntax does not support ON clause; adding ON causes syntax error.

2. Step 2: Understand error cause

   ON clause is used with INNER or LEFT JOIN, not CROSS JOIN.

3. Final Answer:

   Syntax error because CROSS JOIN does not allow ON clause. -> Option C

4. Quick Check:

   CROSS JOIN + ON = syntax error [OK]

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

Common Mistakes:

• Thinking CROSS JOIN can have ON clause
• Confusing CROSS JOIN with INNER JOIN
• Expecting filtered results from CROSS JOIN