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.