CROSS JOIN in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of CROSS JOIN with two small tables

Given two tables Colors and Shapes:

Colors:
id | name
1 | Red
2 | Blue

Shapes:
id | name
1 | Circle
2 | Square

What is the output of this query?

SELECT Colors.name AS Color, Shapes.name AS Shape FROM Colors CROSS JOIN Shapes;

MySQL

SELECT Colors.name AS Color, Shapes.name AS Shape FROM Colors CROSS JOIN Shapes;

A [{"Color": "Red", "Shape": "Square"}, {"Color": "Blue", "Shape": "Square"}]

B [{"Color": "Red", "Shape": "Circle"}, {"Color": "Blue", "Shape": "Square"}]

C [{"Color": "Red", "Shape": "Circle"}, {"Color": "Red", "Shape": "Square"}, {"Color": "Blue", "Shape": "Circle"}, {"Color": "Blue", "Shape": "Square"}]

D [{"Color": "Red", "Shape": "Circle"}, {"Color": "Blue", "Shape": "Circle"}]

💡 Hint

Remember, CROSS JOIN returns every combination of rows from both tables.

The CROSS JOIN produces all possible pairs between rows of Colors and Shapes. Since Colors has 2 rows and Shapes has 2 rows, the result has 2 x 2 = 4 rows.

🧠 Conceptual

intermediate

Understanding the effect of CROSS JOIN on row count

Table Employees has 5 rows and table Departments has 3 rows.

What will be the number of rows returned by the query:

SELECT * FROM Employees CROSS JOIN Departments;

A 8

B 15

C 5

D 3

💡 Hint

Think about how many combinations each employee can have with departments.

CROSS JOIN returns every combination of rows from both tables. So total rows = 5 employees * 3 departments = 15 rows.

📝 Syntax

advanced

Identify the syntax error in CROSS JOIN query

Which option contains a syntax error in the CROSS JOIN query?

MySQL

SELECT * FROM A CROSS JOIN B;

A SELECT * FROM A CROSS JOIN B;

B SELECT * FROM A CROSSJOIN B;

C SELECT * FROM A CROSS JOINB;

D SELECT * FROM A CROSS JOIN B

💡 Hint

Check the spacing and keywords carefully.

The keyword CROSS JOIN must be two separate words. Options B and C have invalid syntax because 'CROSSJOIN' and 'JOINB' are not valid keywords.

🔧 Debug

advanced

Why does this CROSS JOIN query return no rows?

Given tables Products and Categories, the query below returns zero rows:

SELECT * FROM Products CROSS JOIN Categories WHERE Products.id = Categories.id;

Why does this happen?

A Because there are no matching ids between Products and Categories

B Because CROSS JOIN requires an ON clause instead of WHERE

C Because CROSS JOIN returns no rows when a WHERE clause is present

D Because Products and Categories tables are empty

💡 Hint

Think about what the WHERE clause does after the CROSS JOIN.

The CROSS JOIN produces all combinations, but the WHERE clause filters rows where Products.id equals Categories.id. If no ids match, no rows remain.

❓ optimization

expert

Optimizing a CROSS JOIN with large tables

You have two large tables, Users (100,000 rows) and Events (50,000 rows). You want to find all pairs where Users.id = Events.user_id.

Which query is the most efficient?

A SELECT * FROM Users LEFT JOIN Events ON Users.id = Events.user_id;

B SELECT * FROM Users CROSS JOIN Events WHERE Users.id = Events.user_id;

C SELECT * FROM Users, Events WHERE Users.id = Events.user_id;

D SELECT * FROM Users INNER JOIN Events ON Users.id = Events.user_id;

💡 Hint

Consider how JOIN types affect performance and filtering.

INNER JOIN with ON clause directly joins matching rows efficiently. CROSS JOIN with WHERE creates all combinations first, which is costly.