CROSS JOIN in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from both tables using a CROSS JOIN.

MySQL

SELECT * FROM employees [1] departments;

Drag options to blanks, or click blank then click option'

AINNER JOIN

BRIGHT JOIN

CCROSS JOIN

DLEFT JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using INNER JOIN instead of CROSS JOIN.

Forgetting that CROSS JOIN does not need an ON clause.

The CROSS JOIN keyword returns the Cartesian product of the two tables, combining each row of the first table with every row of the second table.

2fill in blank

medium

Complete the code to count the total number of combinations produced by a CROSS JOIN between two tables.

MySQL

SELECT COUNT(*) FROM products [1] categories;

Drag options to blanks, or click blank then click option'

AINNER JOIN

BCROSS JOIN

CLEFT JOIN

DFULL JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using INNER JOIN which filters rows based on a condition.

Using FULL JOIN which is not supported in MySQL.

The CROSS JOIN produces all possible combinations of rows from both tables, so counting the rows after a CROSS JOIN gives the total number of combinations.

3fill in blank

hard

Fix the error in the query to correctly perform a CROSS JOIN between two tables.

MySQL

SELECT * FROM orders [1] customers;

Drag options to blanks, or click blank then click option'

ACROSS JOIN

BINNER JOIN

CLEFT JOIN

DRIGHT JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using ON clause with CROSS JOIN which causes syntax error.

Confusing CROSS JOIN with INNER JOIN.

CROSS JOIN does not use an ON clause because it returns all combinations of rows. The ON clause should be removed when using CROSS JOIN.

4fill in blank

hard

Fill both blanks to create a query that selects all columns from two tables using CROSS JOIN and filters the result with a WHERE clause.

MySQL

SELECT * FROM sales [1] customers WHERE sales.region [2] 'East';

Drag options to blanks, or click blank then click option'

ACROSS JOIN

BINNER JOIN

C=

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using INNER JOIN instead of CROSS JOIN.

Using '!=' instead of '=' in the WHERE clause.

Use CROSS JOIN to combine all rows, then filter with = in the WHERE clause to select rows where the region is 'East'.

5fill in blank

hard

Fill all three blanks to create a query that uses CROSS JOIN and filters rows where the product price is greater than 100 and the category name is 'Electronics'.

MySQL

SELECT * FROM products [1] categories WHERE products.price [2] 100 AND categories.name [3] 'Electronics';

Drag options to blanks, or click blank then click option'

ACROSS JOIN

B>

C=

DINNER JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using INNER JOIN instead of CROSS JOIN.

Using '<' instead of '>' for price comparison.

Using '!=' instead of '=' for category name.

The CROSS JOIN combines all rows. The WHERE clause filters products with price greater than 100 and categories named 'Electronics'.

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