CROSS JOIN cartesian product in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of CROSS JOIN with two small tables

Given two tables Colors and Shapes, what is the output of the following query?

SELECT Colors.color, Shapes.shape FROM Colors CROSS JOIN Shapes;

Tables:
Colors: {color: 'Red'}, {color: 'Blue'}
Shapes: {shape: 'Circle'}, {shape: 'Square'}

SQL

SELECT Colors.color, Shapes.shape FROM Colors CROSS JOIN Shapes;

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

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

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

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

💡 Hint

Remember, 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. With 2 colors and 2 shapes, total rows = 2 * 2 = 4.

🧠 Conceptual

intermediate

Understanding the number of rows in a CROSS JOIN

If table A has 5 rows and table B has 3 rows, how many rows will the query SELECT * FROM A CROSS JOIN B; return?

A 5

B 2

C 8

D 15

💡 Hint

Think about how many pairs you can make combining rows from both tables.

CROSS JOIN returns every combination of rows from both tables, so total rows = 5 * 3 = 15.

📝 Syntax

advanced

Identify the correct CROSS JOIN syntax

Which of the following SQL queries correctly performs a CROSS JOIN between tables Employees and Departments?

A SELECT * FROM Employees CROSS JOIN Departments;

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

C SELECT * FROM Employees INNER JOIN Departments;

D SELECT * FROM Employees, Departments WHERE Employees.id = Departments.id;

💡 Hint

CROSS JOIN does not require an ON clause.

Option A uses the correct CROSS JOIN syntax. Options B and D use INNER JOIN or implicit join with conditions, which are not CROSS JOIN. Option A is incomplete syntax.

🔧 Debug

advanced

Why does this CROSS JOIN query produce an error?

Consider the query:

SELECT a.name, b.name FROM Authors a CROSS JOIN Books b WHERE a.id = b.author_id;

It produces an error: "ambiguous column name 'name'". Why?

A The query is missing a GROUP BY clause.

B The alias 'a' and 'b' are not allowed in CROSS JOIN queries.

C Both tables have a column named 'name' and the query does not specify which one to use in SELECT.

D CROSS JOIN cannot be used with WHERE clauses.

💡 Hint

Check the column names and how they are referenced in SELECT.

Both Authors and Books tables have a 'name' column. The query selects 'a.name' and 'b.name' which is correct, but if the query was written as SELECT name, name it would be ambiguous. The error suggests the query might have SELECT name, name without aliases.

❓ optimization

expert

Optimizing a CROSS JOIN query with large tables

You have two large tables, Products with 100,000 rows and Colors with 50 rows. You want to list all product-color combinations but the query is slow:

SELECT p.id, c.color FROM Products p CROSS JOIN Colors c;

Which approach can improve performance?

A Filter Products or Colors before the CROSS JOIN to reduce rows.

B Use INNER JOIN instead of CROSS JOIN without any ON condition.

C Add an index on Products.id and Colors.color columns.

D Replace CROSS JOIN with UNION ALL of Products and Colors.

💡 Hint

Think about reducing the number of rows before joining.

Filtering rows before CROSS JOIN reduces the number of combinations and speeds up the query. Indexes don't help much for CROSS JOIN because it produces all combinations. INNER JOIN without ON is invalid. UNION ALL combines rows vertically, not horizontally.