Cross Join in MySql
CROSS JOIN in MySQL
A CROSS JOIN in MySQL returns the Cartesian product of two tables, meaning every row from the first table is combined with every row from the second table.
1. Syntax
SELECT * FROM table1 CROSS JOIN table2;
OR
SELECT * FROM table1, table2; -- Implicit CROSS JOIN
- No ON condition is used.
- The number of rows in the result =
rows in table1 × rows in table2.
2. Example: CROSS JOIN Usage
Tables:
employees
| id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
departments
| dept_id | dept_name |
|---|---|
| 101 | IT |
| 102 | HR |
Query: CROSS JOIN employees & departments
SELECT employees.name, departments.dept_nameFROM employeesCROSS JOIN departments;
✅ Output:
+--------+-----------+| name | dept_name |+--------+-----------+| Alice | IT || Alice | HR || Bob | IT || Bob | HR |+--------+-----------+
- 2 employees × 2 departments = 4 rows.
3. CROSS JOIN vs. INNER JOIN
| JOIN Type | Description | Requires ON Condition? |
|---|---|---|
| CROSS JOIN | Matches every row from Table A to every row from Table B (Cartesian product). | ❌ No |
| INNER JOIN | Matches rows based on a condition (common values). | ✅ Yes |
INNER JOIN Example (Filtering Results)
SELECT employees.name, departments.dept_nameFROM employeesINNER JOIN departments ON employees.id = departments.dept_id;
✅ Returns only matching rows.
4. When to Use CROSS JOIN
✅ Generating all possible combinations (e.g., test cases).
✅ Creating permutations of data.
✅ When no relationship exists between tables.
❌ Avoid CROSS JOIN on large tables, as it generates huge results.
