CROSS JOIN behavior in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of CROSS JOIN with two small tables

Given two tables Colors and Shapes with the following rows:

Colors:
id | name
1 | Red
2 | Blue

Shapes:
id | name
1 | Circle
2 | Square

What is the output of the query:

SELECT Colors.name AS color, Shapes.name AS shape FROM Colors CROSS JOIN Shapes ORDER BY color, shape;

A [{"color": "Red", "shape": "Circle"}, {"color": "Blue", "shape": "Circle"}, {"color": "Red", "shape": "Square"}, {"color": "Blue", "shape": "Square"}]

B [{"color": "Blue", "shape": "Circle"}, {"color": "Blue", "shape": "Square"}, {"color": "Red", "shape": "Circle"}, {"color": "Red", "shape": "Square"}]

C [{"color": "Red", "shape": "Circle"}, {"color": "Red", "shape": "Square"}, {"color": "Blue", "shape": "Circle"}, {"color": "Blue", "shape": "Square"}]

D [{"color": "Circle", "shape": "Red"}, {"color": "Circle", "shape": "Blue"}, {"color": "Square", "shape": "Red"}, {"color": "Square", "shape": "Blue"}]

💡 Hint

Remember that CROSS JOIN pairs every row from the first table with every row from the second table.

CROSS JOIN produces all combinations of rows from both tables. Since we order by color then shape, Blue rows come before Red rows alphabetically, so Blue pairs appear first.

🧠 Conceptual

intermediate

Understanding row count after CROSS JOIN

If table A has 5 rows and table B has 3 rows, how many rows will the result have after performing a CROSS JOIN between A and B?

A 15 rows

B 8 rows

C 5 rows

D 3 rows

💡 Hint

Think about how many pairs you can make by combining each row from A with each row from B.

CROSS JOIN pairs every row from A with every row from B, so total rows = 5 * 3 = 15.

📝 Syntax

advanced

Identify the syntax error in CROSS JOIN query

Which of the following queries will cause a syntax error in PostgreSQL?

A SELECT * FROM table1 CROSS JOIN;

B SELECT * FROM table1, table2;

C SELECT * FROM table1 CROSS JOIN table2 ON table1.id = table2.id;

D SELECT * FROM table1 CROSS JOIN table2;

💡 Hint

Check if the CROSS JOIN clause is complete with both table names.

Option A is missing the second table name after CROSS JOIN, causing a syntax error. Option A is valid syntax but the ON clause is ignored in CROSS JOIN, so no error. Option A uses implicit join syntax and is valid.

❓ optimization

advanced

Performance impact of CROSS JOIN on large tables

You have two large tables: Employees with 10,000 rows and Departments with 100 rows. You run a CROSS JOIN between them without any filtering. What is the main performance concern?

A The query will fail because CROSS JOIN is not allowed on large tables.

B The query will return 10,000 rows, which is easy to handle.

C The query will return 100 rows, so performance impact is minimal.

D The query will return 1,000,000 rows, which can cause high memory and CPU usage.

💡 Hint

Think about how many combinations are created by pairing every row from both tables.

CROSS JOIN returns the Cartesian product, so 10,000 * 100 = 1,000,000 rows. This large result can cause high resource usage and slow performance.

🔧 Debug

expert

Why does this CROSS JOIN query return fewer rows than expected?

You have two tables:

Authors: 4 rows
Books: 5 rows

You run this query:

SELECT * FROM Authors CROSS JOIN Books WHERE Authors.id = Books.author_id;

But the result has only 5 rows. Why?

A The query is invalid because CROSS JOIN cannot have a WHERE clause.

B CROSS JOIN always returns the smaller table's row count, so 5 rows is expected.

C The WHERE clause filters the CROSS JOIN result to only matching author-book pairs, reducing rows.

D The database automatically converts CROSS JOIN to INNER JOIN, so only matching rows appear.

💡 Hint

Consider how WHERE filters affect the Cartesian product result.

The CROSS JOIN creates all combinations (4*5=20 rows), but the WHERE clause keeps only rows where Authors.id matches Books.author_id, resulting in 5 rows.

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