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.

Practice

1. What does a CROSS JOIN do in MySQL?

easy

A. Joins tables based on matching column values.

B. Joins tables using a condition on a specific column.

C. Returns only rows that exist in both tables.

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

Solution

1. Step 1: Understand the definition of CROSS JOIN

   A CROSS JOIN returns the Cartesian product of two tables, pairing each row of the first table with every row of the second table.

2. Step 2: Compare with other join types

   Other joins like INNER JOIN or LEFT JOIN use matching conditions, but CROSS JOIN does not use any condition and pairs all rows.

3. Final Answer:

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

4. Quick Check:

   CROSS JOIN = all combinations [OK]

Hint: CROSS JOIN = all possible pairs between tables [OK]

Common Mistakes:

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

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

easy

A. SELECT * FROM table1 CROSS JOIN table2;

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

C. SELECT * FROM table1 INNER JOIN table2 WHERE table1.id = table2.id;

D. SELECT * FROM table1 LEFT JOIN table2 USING (id);

Solution

1. Step 1: Identify the CROSS JOIN syntax

   The correct syntax for CROSS JOIN is: SELECT * FROM table1 CROSS JOIN table2;

2. Step 2: Differentiate from other join syntaxes

   Other options use INNER JOIN or LEFT JOIN with conditions, which are not CROSS JOIN syntax.

3. Final Answer:

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

4. Quick Check:

   Use CROSS JOIN keyword for Cartesian product [OK]

Hint: Use CROSS JOIN keyword without ON clause [OK]

Common Mistakes:

• Using ON clause with CROSS JOIN
• Confusing CROSS JOIN with INNER JOIN syntax
• Using WHERE instead of ON for joins

3. Given two tables:
Table A: id
1
2

Table B: color
'Red'
'Blue'

What is the result of SELECT * FROM A CROSS JOIN B;?

medium

A. Four rows: (1, 'Red'), (1, 'Blue'), (2, 'Red'), (2, 'Blue')

B. Two rows: (1, 'Blue'), (2, 'Red')

C. No rows, because no join condition

D. Two rows: (1, 'Red'), (2, 'Blue')

Solution

1. Step 1: Understand CROSS JOIN output

   CROSS JOIN pairs every row from Table A with every row from Table B, creating all combinations.

2. Step 2: Calculate combinations

   Table A has 2 rows, Table B has 2 rows, so total rows = 2 * 2 = 4. The pairs are (1, 'Red'), (1, 'Blue'), (2, 'Red'), (2, 'Blue').

3. Final Answer:

   Four rows: (1, 'Red'), (1, 'Blue'), (2, 'Red'), (2, 'Blue') -> Option A

4. Quick Check:

   Rows = 2 * 2 = 4 [OK]

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

Common Mistakes:

• Assuming CROSS JOIN filters rows
• Counting only matching rows
• Ignoring all combinations

4. Consider this query:
SELECT * FROM employees CROSS JOIN departments ON employees.id = departments.id;
What is the error in this query?

medium

A. The query will return only matching rows between employees and departments.

B. The ON clause is correct and filters rows properly.

C. CROSS JOIN cannot have an ON clause; it causes a syntax error.

D. The query will return no rows because of wrong ON condition.

Solution

1. Step 1: Check CROSS JOIN syntax rules

   CROSS JOIN does not accept an ON clause because it returns all combinations without filtering.

2. Step 2: Identify the error

   Using ON with CROSS JOIN causes a syntax error in MySQL.

3. Final Answer:

   CROSS JOIN cannot have an ON clause; it causes a syntax error. -> Option C

4. Quick Check:

   ON clause invalid with CROSS JOIN [OK]

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

Common Mistakes:

• Adding ON clause to CROSS JOIN
• Confusing CROSS JOIN with INNER JOIN
• Expecting filtered results from CROSS JOIN

5. You have two tables:
Products (product_id, name) with 3 rows,
Colors (color_id, color_name) with 4 rows.
You want to generate a list of all possible product-color combinations.
Which query correctly achieves this?

hard

A. SELECT product_id, color_name FROM Products INNER JOIN Colors ON Products.product_id = Colors.color_id;

B. SELECT product_id, color_name FROM Products CROSS JOIN Colors;

C. SELECT product_id, color_name FROM Products LEFT JOIN Colors ON Products.product_id = Colors.color_id;

D. SELECT product_id, color_name FROM Products JOIN Colors WHERE Products.product_id = Colors.color_id;

Solution

1. Step 1: Understand the goal

   We want all possible combinations of products and colors, which means pairing every product with every color.

2. Step 2: Choose the correct join type

   CROSS JOIN produces the Cartesian product, giving all combinations. INNER JOIN or LEFT JOIN with conditions only return matching rows, which is not desired here.

3. Final Answer:

   SELECT product_id, color_name FROM Products CROSS JOIN Colors; -> Option B

4. Quick Check:

   Use CROSS JOIN for all combinations [OK]

Hint: Use CROSS JOIN to get all product-color pairs [OK]

Common Mistakes:

• Using INNER JOIN expecting all combinations
• Adding ON condition that filters rows
• Using WHERE instead of CROSS JOIN