MySQL CROSS JOIN

MySQL CROSS JOIN

The CROSS JOIN in MySQL is used to return the Cartesian product of two tables.

•  It combines each row of the first table with every row of the second table.

 What is CROSS JOIN?

• Produces all possible combinations of rows

• No join condition (ON) is required

• Result rows =
  rows_in_table1 × rows_in_table2

 Also called Cartesian Join

 Basic Syntax

• CROSS JOIN and FROM table1, table2 behave the same.

 Simple Example

Table: colors

Table: sizes

Query

Result

•  2 × 3 = 6 rows

 CROSS JOIN Without Keyword

•  This is an implicit CROSS JOIN

 CROSS JOIN with WHERE Clause

You can filter results after CROSS JOIN.

•  Still a CROSS JOIN, but filtered result

 CROSS JOIN vs INNER JOIN (Interview Favorite)

 When to Use CROSS JOIN?

•  Generating combinations (sizes × colors)
• Creating test data
•  Matrix-style reports
•  Scheduling / permutation problems

 Avoid with large tables (performance risk)

 Performance Warning

If:

• Table A has 1,000 rows

• Table B has 1,000 rows

•  Result = 1,000,000 rows

 Always check table sizes before using CROSS JOIN.

CROSS JOIN vs UNION

 Common Mistakes

•  Using CROSS JOIN unintentionally
•  Forgetting result size explosion
•  Using instead of INNER JOIN
•  Running on large tables in production

Interview Questions & MCQs (Very Important)

Q1. What does CROSS JOIN return?

A) Matching rows
B) Unique rows
C) Cartesian product
D) Filtered rows

Answer: C

Q2. Is ON condition required in CROSS JOIN?

A) Yes
B) No

Answer: B

Q3. If table A has 5 rows and table B has 4 rows, result rows?

A) 9
B) 20
C) 5
D) 4

Answer: B

Q4. Which join can cause huge result sets?

A) INNER JOIN
B) LEFT JOIN
C) RIGHT JOIN
D) CROSS JOIN

Answer: D

Q5. Which syntax is equivalent to CROSS JOIN?

A) FROM a INNER JOIN b
B) FROM a LEFT JOIN b
C) FROM a, b
D) FROM a RIGHT JOIN b

Answer: C

Q6. CROSS JOIN is also known as?

A) Natural Join
B) Equi Join
C) Cartesian Join
D) Self Join

Answer: C

Real-Life Use Cases

•  Product variants (color × size)
•  Time-slot generation
•  Test data creation
•  Combinatorial reports

 Summary

• CROSS JOIN returns Cartesian product

• No join condition required

• Rows = multiplication of table sizes

• Powerful but dangerous if misused

• Important for SQL exams & interviews