CROSS JOIN in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does a CROSS JOIN do in SQL?

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

beginner

How many rows will the result have when you CROSS JOIN a table with 3 rows and another with 4 rows?

The result will have 3 × 4 = 12 rows, because each row from the first table pairs with every row from the second table.

beginner

Write a simple SQL query using CROSS JOIN for tables 'A' and 'B'.

SELECT * FROM A CROSS JOIN B;

intermediate

What is the difference between CROSS JOIN and INNER JOIN?

CROSS JOIN returns all combinations of rows from both tables without any condition. INNER JOIN returns only rows where there is a match based on a condition.

intermediate

Can CROSS JOIN be used without the keyword CROSS? If yes, how?

Yes, you can write CROSS JOIN by listing tables separated by commas in the FROM clause, like: SELECT * FROM A, B; which behaves like a CROSS JOIN.

What does a CROSS JOIN return?

A All possible combinations of rows from both tables

B Only matching rows based on a condition

C Rows from the first table only

D Rows from the second table only

A CROSS JOIN returns the Cartesian product, meaning every row from the first table combined with every row from the second table.

If table X has 5 rows and table Y has 2 rows, how many rows will SELECT * FROM X CROSS JOIN Y return?

A 2

B 10

C 7

D 5

The result will have 5 × 2 = 10 rows because CROSS JOIN pairs every row from X with every row from Y.

Which SQL keyword is used to perform a CROSS JOIN explicitly?

A CROSS JOIN

B LEFT JOIN

C INNER JOIN

D OUTER JOIN

The keyword CROSS JOIN explicitly performs a Cartesian product of two tables.

What is the result of SELECT * FROM A, B; in SQL?

A An INNER JOIN

B A LEFT JOIN

C A CROSS JOIN

D An error

Listing tables separated by commas in FROM clause performs a CROSS JOIN (Cartesian product).

Which join type should you use if you want only matching rows between two tables?

A CARTESIAN JOIN

B CROSS JOIN

C FULL JOIN

D INNER JOIN

INNER JOIN returns only rows where there is a match based on a condition, unlike CROSS JOIN which returns all combinations.

Explain what a CROSS JOIN does and give a simple example query.

Think about pairing every row from one table with every row from another.

Describe the difference between CROSS JOIN and INNER JOIN in SQL.

Consider when you want all pairs versus only related pairs.

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