MySQL CROSS JOIN

MySQL Tutorial

❌➕ 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.

1️⃣ 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

2️⃣ Basic Syntax


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

3️⃣ Simple Example ⭐

Table: colors

color
Red
Blue

Table: sizes

size
S
M
L

Query


Result

color size
Red S
Red M
Red L
Blue S
Blue M
Blue L

✔ 2 × 3 = 6 rows

4️⃣ CROSS JOIN Without Keyword ❓


📌 This is an implicit CROSS JOIN

5️⃣ CROSS JOIN with WHERE Clause

You can filter results after CROSS JOIN.


✔ Still a CROSS JOIN, but filtered result

6️⃣ CROSS JOIN vs INNER JOIN ⭐ (Interview Favorite)

Feature CROSS JOIN INNER JOIN
Join condition ❌ No ✔ Yes
Result Cartesian product Matched rows
Rows count Multiplication Depends on match
Risk Very high Controlled

7️⃣ When to Use CROSS JOIN?

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

❌ Avoid with large tables (performance risk)

8️⃣ 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.

9️⃣ CROSS JOIN vs UNION ⭐

Feature CROSS JOIN UNION
Combines Rows × Rows Rows + Rows
Direction Horizontal multiplication Vertical append
Condition ❌ None Column match

🔟 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

You may also like...